To select specific rows from a table, after the SELECT statement use the WHERE clause to specify the condition or conditions that a row must meet to be selected. A criterion for selecting rows from a table is a search condition.
A search condition consists of one or more predicates. A
predicate specifies a condition that is true or false (or unknown) about a
row. You can specify conditions in the WHERE clause by using the
following basic predicates:
| Predicate | Function |
|---|---|
| x = y | x is equal to y |
| x <> y | x is not equal to y |
| x < y | x is less than y |
| x > y | x is greater than y |
| x <= y | x is less than or equal to y |
| x >= y | x is greater than or equal to y |
| IS NULL/IS NOT NULL | tests for null values |
When you construct search conditions, be careful to perform arithmetic operations only on numeric data types, and to make comparisons only among compatible data types. For example, you can't compare strings to numbers.
If you are selecting rows based on a character value, that value must be enclosed in single quotation marks (for example, WHERE JOB = 'Clerk') and each character value must be typed exactly as it exists in the database. If the data value is lowercase in the database and you type it as uppercase, no rows will be selected. If you are selecting rows based on a numeric value, that value must not be enclosed in quotation marks (for example, WHERE DEPT = 20).
The following example selects only the rows for department 20 from the STAFF table:
SELECT DEPT, NAME, JOB
FROM STAFF
WHERE DEPT = 20
This statement produces the following result:
DEPT NAME JOB
------ --------- -----
20 Sanders Mgr
20 Pernal Sales
20 James Clerk
20 Sneider Clerk
The next example uses AND to specify more than one condition. You can specify as many conditions as you want. The example selects clerks in department 20 from the STAFF table:
SELECT DEPT, NAME, JOB
FROM STAFF
WHERE JOB = 'Clerk'
AND DEPT = 20
This statement produces the following result:
DEPT NAME JOB
------ --------- -----
20 James Clerk
20 Sneider Clerk
A null value occurs where no value is entered and the column does not support a default value. It can also occur where the value is specifically set to null. It can occur only in columns that are defined to support null values. Defining and supporting null values in tables are discussed in Creating Tables.
Use the predicate IS NULL, and IS NOT NULL to check for a null value.
The following statement lists employees whose commission is not known:
SELECT ID, NAME
FROM STAFF
WHERE COMM IS NULL
This statement produces the following result:
ID NAME
------ ---------
10 Sanders
30 Marenghi
50 Hanes
100 Plotz
140 Fraye
160 Molinare
210 Lu
240 Daniels
260 Jones
270 Lea
290 Quill
The value zero is not the same as the null value. The following statement selects everyone in a table whose commission is zero:
SELECT ID, NAME
FROM STAFF
WHERE COMM = 0
Because there are no values of zero in the COMM column in the sample table, the result set returned is empty.
The next example selects all rows where the value of YEARS in the STAFF table is greater than 9:
SELECT NAME, SALARY, YEARS
FROM STAFF
WHERE YEARS > 9
This statement produces the following result:
NAME SALARY YEARS
--------- --------- ------
Hanes 20659.80 10
Lu 20010.00 10
Jones 21234.00 12
Quill 19818.00 10
Graham 21000.00 13