syntax and example of combining AND with OR condition in oracle sql

The AND and OR condition in Oracle SQL / PLSQL can be combined in a WHERE clause to get the desired results from a SQL query.
The combination of AND with OR condition can be used with SELECT, INSERT, UPDATE and DELETE statements.
The most important part is to place the “(“ and “)” brackets at correct positions so that the required result can be fetched.

Syntax for the AND with OR condition in Oracle SQL / PLSQL is:

SELECT column_name(s)
FROM table_name
WHERE (condition1 and condition2)
OR (condition3 and condition4)
.
.
OR (conditionN-1 and conditionN);

INSERT INTO table_name
VALUES(column_name1
,colum_name2
,column_name3
.
.
column_nameN)
WHERE (condition1 and condition2)
OR (condition3 and condition4)
.
.
OR (conditionN-1 and conditionN);

UPDATE table_name
SET (column_name1 = value/expression
,column_name2 = value/expression
, column_name3 = value/expression
.
.
)
WHERE (condition1 and condition2)
OR (condition3 and condition4)
.
.
OR (conditionN-1 and conditionN);

DELETE FROM table_name
WHERE (condition1 and condition2)
OR (condition3 and condition4)
.
.
OR (conditionN-1 and conditionN);

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 employee(s) having Salary more than ‘20000’ and working in the ‘IT’ department or the employee(s) having Salary less than ‘30000’ and working in ‘Sales’ department then we can achieve the same using OR condition as follows:

SELECT *
FROM employee
WHERE (salary > 20000 AND department = 'IT')
OR    (salary < 30000 AND department = 'Sales');

The result of the above query will be:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
103	Emp C	28000	IT	20

Here we have retrieved the records pertaining to employee having Salary more than ‘20000’ and working in the ‘IT’ department or the employee(s) having Salary less than ‘30000’ and working in ‘Sales’ department using combination of AND with OR condition along with WHERE clause.

Scenario 2:

We can exchange the place for AND / OR conditions in a query.
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 of employees who are having Salary > ‘20000’, and working in ‘IT’ department or having Salary < ‘30000’or working in Sales department. We can achieve the same as: [sourcecode language="sql" light="true"] SELECT * FROM employee [/sourcecode][sourcecode language="sql" light="true"] WHERE (salary > 20000 AND department = 'IT') OR (salary < 30000 OR department = 'Sales'); [/sourcecode] 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 employees who are having Salary > ‘20000’, and working in ‘IT’ department or having Salary < ‘30000’or working in 'Sales' department by using AND with OR condition in WHERE clause.