SQL: AND & OR Conditions

SQL tutorial explains how to use AND and OR condition together in a query with syntax and examples.

Description

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)

Syntax

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.

For example:

SELECT *

FROM employee

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.

For example:

SELECT employee_id

FROM employee

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 statement.

For example:

INSERT INTO Employee

(Employee_id, Employee_name)

SELECT account_no, customer_name

FROM customers

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.

For example:

UPDATE Employee

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.

For example:

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.