Using NULL Values in the WHERE Clause

A row gets selected if the search condition specified using the WHERE clause evaluates to TRUE and not if evaluated to FALSE or unknown.

Consider the following WHERE clause:

WHERE ((sal + commission) < 5000)

     AND empno = 2004  ;

In the above example, if commission is NULL for empno 2004, then the search condition is not satisfied. Instead, the following search condition with an OR would evaluate to TRUE:

WHERE ((sal + commission) < 5000)

     OR empno = 2004  ;

A row containing NULL values can be selected or rejected using the search condition:

column IS [NOT] NULL

The following example shows the selection of employee names and department numbers for employees who have not been assigned to any project.

EXEC SQL

     SELECT ename, deptno

     FROM employee

     WHERE projno IS NULL ;

If a join is performed between two tables using the WHERE clause:

WHERE column1 = column2

Rows will not be selected if either of the columns column1 or column2 is NULL. Also, rows will not be selected if both column1 and column2 are NULL.