The WHERE clause: specifying rows

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 emp_lname
FROM employee
WHERE salary > 50000
```
Ranges (BETWEEN and NOT BETWEEN) For example, you can list all employees earning between $40,000 and $60,000:
```
SELECT emp_lname
FROM employee
WHERE salary BETWEEN 40000 AND 60000
```
Lists (IN, NOT IN) For example, you can list all customers in Ontario, Quebec, or Manitoba:
```
SELECT company_name , state
FROM customer
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 company_name , phone
FROM customer
WHERE phone LIKE '415%'
```
Unknown values (IS NULL and IS NOT NULL) For example, you can list all departments with managers:
```
SELECT dept_name
FROM Department
WHERE dept_head_id 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 emp_fname, emp_lname
FROM employee
WHERE salary > 50000
AND emp_fname like 'A%'
```

In addition, the WHERE keyword can introduce the following:

Transact-SQL join conditions Joins are discussed in Joins: Retrieving Data from Several Tables.

For more information about search conditions, see Search conditions.

The following sections describe how to use WHERE clauses.