syntax and example of where clause in oracle sql

The WHERE clause in SQL / PLSQL allows us to filter the records from the result of a query; WHERE clause can be used with a SELECT, INSERT, UPDATE and DELETE statements.

Syntax for the WHERE clause in SQL / PLSQL is:

SELECT column_name(s)
FROM table_name
WHERE conditions;

INSERT INTO table_name
VALUES(column_name1
,column_name2
,column_name3
.
.
,column_nameN)
WHERE conditions;

UPDATE table_name
SET (column_name1 = value/expression
,column_name2 = value/expression
,column_name3 = value/expression
.
.
)
WHERE conditions;

DELETE FROM table_name
WHERE conditions;

Let’s take an example for understanding:
Suppose have a table named ‘employee’ in the database as shown below.

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Scenario 1:

Suppose we want to see the records of only ‘Sales’ department then we can achieve the same using WHERE clause as follows:

SELECT *
FROM employee
WHERE department = 'Sales';

The result of the above query will be:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
105	Emp E	32000	Sales	10

Here we have retrieved the records pertaining to ‘Sales’ department only using WHERE clause.

Scenario 2:

WHERE clause can also be used with combination of columns
Let’s assume that we have a new table namely ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Suppose we want to view all the records from the departments ‘Sales’ and ‘IT’.

We can achieve the same as:

SELECT *
FROM employee
WHERE department = 'Sales'
OR    department ='IT';

Once we have run the above code following will be the result:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
105	Emp E	32000	Sales	10

Here we have successfully retrieved the records pertaining to ‘Sales’ and ‘IT’ department using WHERE clause.