WHERE

Use WHERE in your SELECT statement to allow QMF to select just those rows from a table that meet a certain condition or set of conditions, without retrieving every row in a table. The WHERE clause specifies a search condition (one or more selection criteria) that identifies the row or rows you want to retrieve, update, or delete.

The search condition of a WHERE clause specifies that a comparison be made between two values. Usually, a column's value is compared with a fixed value specified in the WHERE clause. The only rows selected are the ones that satisfy the search condition. 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

Both WHERE and HAVING eliminate data 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 row data.

• 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.

For example, to list the smallest, largest, and average salary in each department, excluding managers, for departments with an average salary greater than $12,000:

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  MIN(SALARY)  MAX(SALARY)         AVG(SALARY)
 ------  -----------  -----------  ------------------
     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

In addition to making an equality comparison (=), you can compare a column value in the following ways. The condition defined in the first column is specified by entering the corresponding words or symbols in the second column.

Condition

Word or Symbol

Equal to

=

Not equal to

< >

Alternative to not equal to

¬=

Greater than

>

Greater than or equal to

>=

Not greater than

¬> (in DB2 only)

Less than

<

Less than or equal to

<=

Not less than

¬< (in DB2 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

LIKE '%abc%'

Ignores certain characters

LIKE '_a_'

Negative conditions

NOT

A not sign (¬) can cause parsing errors in statements passed from one DBMS 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 quotes (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.