Specifying a search condition using the WHERE clause

 

The WHERE clause specifies a search condition that identifies the row or rows that you want to retrieve, update, or delete.

The number of rows you process with an SQL statement then depends on the number of rows that satisfy the WHERE clause search condition. A search condition consists of one or more predicates. A predicate specifies a test that you want SQL to apply to a specified row or rows of a table.

In the following example, WORKDEPT = 'C01' is a predicate, WORKDEPT and 'C01' are expressions, and the equal sign (=) is a comparison operator. Note that character values are enclosed in apostrophes ('); numeric values are not. This applies to all constant values wherever they are coded within an SQL statement. For example, to specify that you are interested in the rows where the department number is C01, issue the following statement:

… WHERE WORKDEPT = 'C01'

In this case, the search condition consists of one predicate: WORKDEPT = 'C01'.

To further illustrate WHERE, put it into a SELECT statement. Assume that each department listed in the CORPDATA.DEPARTMENT table has a unique department number. You want to retrieve the department name and manager number from the CORPDATA.DEPARTMENT table for department C01. Issue the following statement:

SELECT DEPTNAME, MGRNO        FROM CORPDATA.DEPARTMENT        WHERE DEPTNO = 'C01'

The result of this statement is one row.

Table 1. Result table

DEPTNAME	MGRNO
INFORMATION CENTER	000030

If the search condition contains character, or UCS-2 or UTF-16 graphic column predicates, the sort sequence that is in effect when the query is run is applied to those predicates. If a sort sequence is not being used, character constants must be specified in uppercase or lowercase to match the column or expression they are being compared to.

• Expressions in the WHERE clause
  An expression in a WHERE clause names or specifies something that you want to compare to something else.

• Comparison operators
  SQL supports several comparison operators.

• NOT keyword
  You can precede a predicate with the NOT keyword to specify that you want the opposite of the predicate's value (that is, TRUE if the predicate is FALSE).

 

Parent topic:

Retrieving data using the SELECT statement

 

Related concepts

Sort sequences and normalization in SQL

 

Related reference

Defining complex search conditions
Multiple search conditions within a WHERE clause