iAnywhere.com - 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 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.