WHERE

Use a WHERE clause in your SELECT statement to specify a condition (one or more selection criteria) that identifies the row or rows you want to retrieve, update, or delete. Only the rows that satisfy the search condition are affected.

Both WHERE and HAVING eliminate data that you do not want in your report:

The WHERE condition is used with column selection. It determines whether an individual row is included.
Use WHERE to eliminate unwanted rows.
The HAVING condition is used with built-in functions. It determines whether a whole group is included.
HAVING is always followed by a column function (such as SUM, AVG, MAX, MIN, or COUNT). HAVING can also be followed by a subquery that finds a grouped value to complete the HAVING condition.

Use HAVING to eliminate unwanted grouped data.

You can compare column values by using any of the operators that are shown in the following table. The condition that is defined in the first column is specified by entering the corresponding words or symbols in the second column.

Table 1. Comparison operators
Comparison	Comparison operator to use
Equal to	=
Not equal to	<> or ¬=
Greater than	>
Greater than or equal to	>=
Not greater than (Db2® for z/OS® only)	¬>
Less than	<
Less than or equal to	<=
Not less than (Db2 for z/OS only)	¬<
Multiple conditions	AND OR
Values within a range	BETWEEN x AND y
Values matching any in a list	IN (x, y, z)
Selects a string of characters	% (example: LIKE '%abc%')
Ignores certain characters	_ (example: LIKE '_a_')
Negative conditions	NOT

A not sign (¬) can cause parsing errors in statements that are passed from one database management system to another. To avoid this possible problem in statements to be executed at a remote location, substitute an equivalent for any operation in which the not sign appears. For example, substitute <> for ¬=, <= for ¬>, and >= for ¬<.

Values to be compared with columns of character data must be enclosed in single quotation marks (as in WHERE NAME = 'JONES'). Numeric data is not enclosed in quotes.

If you are using graphic data, the value after WHERE must be preceded by the single-byte character 'G' and be enclosed in single quotation marks. The percent sign and the underscore must be double-byte characters.

Examples

Here are some examples of how to use a WHERE clause in a query:

In the following example, the search condition specifies that the value in the DEPT column must be 20. This query:

SELECT DEPT, NAME, JOB
FROM Q.STAFF
WHERE DEPT = 20

Produces this report:

DEPT NAME        JOB
---- ----------  -----
 20  SANDERS     MGR
 20  PERNAL      SALES
 20  JAMES       CLERK
 20  SNEIDER     CLERK

To list the least, greatest, and average salary in each department, excluding managers, for departments with an average salary greater than $12,000, use the following query. This query:

SELECT DEPT, MIN(SALARY),
 MAX(SALARY), AVG(SALARY)
FROM Q.STAFF
WHERE JOB <> 'MGR'
GROUP BY DEPT
HAVING AVG(SALARY) > 12000

Produces this report:

  DEPT        COL1        COL2                COL3
------  ----------  ----------  ------------------
    15    12258.50    16502.83    13756.5100000000
    20    13504.60    18171.25    15309.5333333333
    38    12009.75    18006.00    14944.7000000000
    42    10505.90    18001.75    13338.7500000000
    51    13369.80    19456.50    16235.2000000000
    66    10988.00    21000.00    16880.1750000000
    84    13030.50    17844.00    15443.0000000000

You can write a WHERE search condition that uses any of the comparison operators. For example, to select only employees who made commissions of $1,000 or more, use a query like the following one. This query:

SELECT ID, COMM
FROM Q.STAFF
WHERE COMM >= 1000

Produces this report:

  ID     COMM
----- -------
  70  1152.00
  90  1386.70
 340  1285.00

To select everyone with 10 years of service or more:

SELECT ID, NAME, YEARS
FROM Q.STAFF
WHERE YEARS >= 10

To select everyone with more than 10 years of service:

SELECT ID, NAME, YEARS
FROM Q.STAFF
WHERE YEARS > 10

To select every manager:

SELECT ID, NAME, YEARS
FROM Q.STAFF
WHERE JOB = 'MGR'

To select everyone whose name occurs later in the alphabet than SMITH:

SELECT NAME, ID
FROM Q.STAFF
WHERE NAME > 'SMITH'

To select every employee name in Q.STAFF that is not in Department 10:

SELECT NAME, ID
FROM Q.STAFF
WHERE DEPT < > 10