Expressions in the WHERE clause

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

The expressions you specify can be:

A column name names a column. For example:
```
... WHERE EMPNO = '000200'
```
EMPNO names a column that is defined as a 6-byte character value.
An expression identifies two values that are added (+), subtracted (-), multiplied (*), divided (/), have exponentiation (**), or concatenated (CONCAT or ||) to result in a value. The most common operands of an expression are:
- A constant
- A column
- A host variable
- A global variable
- A function
- A special register
- A scalar fullselect
- Another expression
For example:
```
... WHERE INTEGER(PRENDATE - PRSTDATE) > 100
```
When the order of evaluation is not specified by parentheses, the expression is evaluated in the following order:
1. Prefix operators
2. Exponentiation
3. Multiplication, division, and concatenation
4. Addition and subtraction
Operators on the same precedence level are applied from left to right.
A constant specifies a literal value for the expression. For example:
```
... WHERE 40000 < SALARY
```
SALARY names a column that is defined as a 9-digit packed decimal value (DECIMAL(9,2)). It is compared to the numeric constant 40000.
A host variable identifies a variable in an application program. For example:
```
... WHERE EMPNO = :EMP
```
A special register identifies a special value defined by the database manager. For example:
```
... WHERE LASTNAME = USER
```
The NULL value specifies the condition of having an unknown value.
```
... WHERE DUE_DATE IS NULL
```

A search condition can specify many predicates separated by AND and OR. No matter how complex the search condition, it supplies a TRUE or FALSE value when evaluated against a row. There is also an unknown truth value, which is effectively false. That is, if the value of a row is null, this null value is not returned as a result of a search because it is not less than, equal to, or greater than the value specified in the search condition.

To fully understand the WHERE clause, you need to know the order SQL evaluates search conditions and predicates, and compares the values of expressions. This topic is discussed in the DB2® for i SQL reference topic collection.