SQL: AND & OR conditions

The scope of the Where clause and the operators used with it can be extended by using the logical operators AND and OR. Compound conditions are made up of multiple simple conditions connected by AND or OR. There is no limit to the number of simple conditions that can be present in a single SQL statement. They enable you to specify compound search conditions to fine tune your data retrieval requirements.

The syntax for a compound condition is as follows:

SELECT column_name 
FROM table_name 
WHERE simple condition 
{[AND|OR] simple condition} 

Example #1

SELECT * FROM AntiqueOwners 
WHERE OwnerLastName = 'Smith' 
AND OwnerFirstName = 'Bob' 

This SQL statement returns all antique owners with the first name equal to “Bob” and the last name equal to “Smith”.

Example #2

SELECT EmployeeIDNo FROM EmployeeStatisticsTable 
WHERE Position = 'Manager' 
OR Salary > 60000 

Retrieve all employee ids whose position is Manager or who have a Salary over 60,000 dollars.

Example #3

SELECT EmployeeIDNo FROM EmployeeStatisticsTable 
WHERE Position = 'Staff' AND Benefits = 12000 
OR Salary < 57000 

You can also combine AND and OR. This will return all employee ids whose position equal to staff and benefits equal to 12,000 dollars or return all employee ids with salary less than 57,000 dollars.