IS NULL / IS NOT NULL
Purpose
Searches for or excludes nulls in an expression.
Syntax
where:
|
Syntax element … |
Specifies … |
|
expression |
an expression that specifies a value that is tested for nulls. |
ANSI Compliance
IS NULL and IS NOT NULL are ANSI SQL:2011 compliant.
Example
To search for the names of all employees who have not been assigned to a department, enter the following statement:
SELECT Name
FROM Employee
WHERE DeptNo IS NULL;
The result of this query is the names of all employees with a null in the DeptNo field.
Example
Conversely, to search for the names of all employees who have been assigned to a department, you could enter the following statement:
SELECT Name
FROM Employee
WHERE DeptNo IS NOT NULL;
This query returns the names of all employees with a non-NULL value in the DeptNo field.
Example : Searching for NULL and NOT-NULL in the Same Statement
If you are searching for NULLs and non-NULL values in the same statement, the search condition for the NULLs must appear separately.
For example, to select the names of all employees without the job title of “Manager” or “Vice Pres”, plus the names of all employees with a null in the JobTitle column, you must enter the statement as follows:
SELECT Name, JobTitle
FROM Employee
WHERE (JobTitle NOT IN ('Manager' OR 'Vice Pres'))
OR (JobTitle IS NULL) ;
Example : Searching a Table That Might Contain Nulls
You must be careful when searching a table that might contain nulls. For example, if the EdLev column contains nulls and you submit the following query, the result contains only the names of employees with an education level of less than 16 years.
SELECT Name, EdLev
FROM Employee
WHERE (EdLev < 16) ;
To ensure that the result of a statement contains nulls, you must structure it as follows.
SELECT Name, EdLev
FROM Employee
WHERE (EdLev < 16)
OR (EdLev IS NULL) ;