NULL Value Equality Check
It is checked whether the field contains NULL value.
NULL values ensure the possibility of ternary logic application under the selection conditions. For any given record the result of selection condition application can be TRUE, FALSE or NULL (in the case when one of the fields contains NULL value). For the explicit check of fields' value for NULL equality and immediate processing of them you can use IS NULL instruction.
Find the employee which is not office dedicated yet.
SELECT name FROM salesreps WHERE rep_office IS NULL
IS NOT NULL instruction gives the possibility to find the records which don't contain NULL values.
Read out the list of employees which are already dedicated to offices.
SELECT name FROM salesreps WHERE rep_office IS NOT NULL
NULL check can't return NULL value as a result. It returns TRUE or FALSE every time.
Note: NULL key word can't be used in the comparison operation =, as in this case we'll get the expression where one of the instructions is NULL, and, therefore the expression result will be NULL:
SELECT name FROM salesreps WHERE rep_office = NULL -- WRONG!