Using the ISNULL Operator in SQL

A null value, as mentioned earlier, is a value that is unavailable or inapplicable. It is neither said to be a zero nor can it be represented as a space. Also, you cannot use an equal to operator (=) in the WHERE clause to match null values. The valid procedure to find a null match is to use the IS NULL operator. In the first two examples below you won’t get any record. In the first statement the equal to (=) comparison operator is used, and in the second one, the statement is tried with an empty space (‘ ‘). In fact, the second query will also throw an “invalid number” error, because you are comparing a numeric column with an empty string. The third query is correct which uses the IS NULL operator to display records of sales representatives. To get negating records (other than sales personnel), use the NOT logical operator in the WHERE clause like this: commission_pct IS NOT NULL.

    SQL Statements :

SELECT       first_name, last_name, commission_pct

FROM          employees

WHERE      commission_pct=NULL;

SELECT       first_name, last_name, commission_pct

FROM          employees

WHERE      commission_pct=’ ‘;

SELECT       first_name, last_name, commission_pct

FROM          employees

WHERE      commission_pct  IS  NULL;

    Output :