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 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 scalar fullselect.

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, know the order SQL evaluates search conditions and predicates, and compares the values of expressions. This topic is discussed in the SQL Reference topic collection.

 

Parent topic:

Specifying a search condition using the WHERE clause

 

Related concepts

Using subqueries

 

Related reference

Defining complex search conditions
Expressions