IS NULL and IS NOT NULL Conditions

The IS NULL condition is satisfied if the term that immediately precedes the IS keyword specifies one of the following undefined values:

  • The name of a column that contains a null value.
  • An expression that evaluates to null.

Conversely, if you use the IS NOT NULL operator, the condition is satisfied when the column contains a value that is not null, or when the expression that immediately precedes the IS NOT NULL keywords does not evaluate to null.

Suppose that you wish to perform an arithmetic computation on a column that can contain NULL values. You can create a table, insert values into the table, and then perform a query that uses a generic CASE expression that converts null values to 0 for the purpose of arithmetic calculations: 

CREATE TABLE employee (emp_id INT, savings_in_401k INT, total_salary INT);  

INSERT INTO employee VALUES(1, 5000, 40000); 
INSERT INTO employee VALUES(2, 0, 40000); 
INSERT INTO employee VALUES(3, NULL, 100000);  

SELECT emp_id, savings_in_401k AS employer_match FROM employee WHERE
    CASE WHEN(savings_in_401k IS NULL) THEN 0
         ELSE savings_in_401k END * 0.06 > 0;
This example shows that by using IS NULL in the CASE expression, you can provide a value for the entries that otherwise are not computable because null is not a valid numeric value.

The IS NULL condition is satisfied if the column contains a null value or if the expression cannot be evaluated because it contains one or more null values. If you use the IS NOT NULL operator, the condition is satisfied when the operand is column value that is not null, or an expression that does not evaluate to null.