SQL tutorial explains how to use AND and OR condition together in a query with syntax and examples.
The SQL AND and OR can be combined in SELECT, INSERT, UPDATE, or DELETE statement.
When combining these conditions, it is important to use brackets so that the database knows what order to evaluate each condition. (Like we are doing in Maths)
The syntax for the SQL AND Condition is:
WHERE condition1 AND condition2
... OR condition_n;
Parameters or Arguments
condition1, condition2, condition_n are the conditions that evaluated to determine if the records will be selected.
SQL Example – On SELECT Statement
combines the AND condition and OR condition in a SELECT query.
WHERE (city = 'delhi' AND CName = 'IBM')
OR (ranking >= 10);
This SQL SELECT example would return all employee that reside in delhi whose company name is IBM and ranking is greater than or equal to 10. The brackets determine the order that the AND and OR conditions are evaluated. The next example takes a look at a more complex statement.
WHERE (CName = 'IBM')
OR (CName = ‘Infosys' AND city = 'Bombay')
OR (name = 'Wipro' AND status = 'Active' AND city = 'Chennai');
This SQL SELECT statement would return all supplier_id values where the supplier's name is IBM or the name is Hewlett Packard and the city is Atlantic City or the name is Gateway, the status is Active, and the city is Burma.
SQL Example - INSERT Statement
example demonstrates how the SQL AND condition and SQL OR condition can be combined in the
INSERT INTO Employee
SELECT account_no, customer_name
WHERE (customer_name = 'Infosys' OR customer_name = 'Wipro')
AND employees > 15;
SQL Example - UPDATE Statement
This example shows how the AND and OR conditions can be used in the UPDATE statement.
SET Cname = 'HP'
WHERE Cname = 'IBM'
AND state = 'California';
This SQL AND & OR condition example would update all Cname values in the employee table to HP where the Cname was IBM and resides in the state of California.
SQL Example - DELETE Statement
Finally, this last AND & OR condition example demonstrates how the AND and OR condition can be used in the DELETE statement.
DELETE FROM employee
WHERE city = 'New York'
AND (designation = 'manager' OR Cname = 'Dell');
This SQL AND and OR condition example would delete all employee from the employee table whose city was New York and either the designation is manager or the company name is Dell.