The WHERE clause in a SELECT statement specifies the search conditions for exactly which rows are retrieved. The general format is:
SELECT select-list
FROM table-list
WHERE search-condition
Search conditions, also called qualifications or predicates, in the WHERE clause include the following:
Comparison operators (=, <, >, and so on) For example, you can list all employees earning more than $50,000:
SELECT Surname FROM Employees WHERE Salary > 50000;
Ranges (BETWEEN and NOT BETWEEN) For example, you can list all employees earning between $40,000 and $60,000:
SELECT Surname FROM Employees WHERE Salary BETWEEN 40000 AND 60000;
Lists (IN, NOT IN) For example, you can list all customers in Ontario, Quebec, or Manitoba:
SELECT CompanyName, State FROM Customers WHERE State IN( 'ON', 'PQ', 'MB');
Character matches (LIKE and NOT LIKE) For example, you can list all customers whose phone numbers start with 415. (The phone number is stored as a string in the database):
SELECT CompanyName, Phone FROM Customers WHERE Phone LIKE '415%';
Unknown values (IS NULL and IS NOT NULL) For example, you can list all departments with managers:
SELECT DepartmentName FROM Departments WHERE DepartmentHeadID IS NOT NULL;
Combinations (AND, OR) For example, you can list all employees earning over $50,000 whose first name begins with the letter A.
SELECT GivenName, Surname FROM Employees WHERE Salary > 50000 AND GivenName like 'A%';
For more information about search conditions, see Search conditions.
Using comparison operators in the WHERE clause
Using ranges in the WHERE clause
Using lists in the WHERE clause
Matching character strings in the WHERE clause
Character strings and quotation marks
Unknown Values: NULL
Connecting conditions with logical operators
Comparing dates in search conditions
Matching rows by sound