IS NULL
The test for whether a value is NULL, which works where = NULL never does.
In more detail
WHERE manager_id IS NULL finds the employees with no manager, while WHERE manager_id = NULL finds nobody. IS NOT NULL is the opposite test. After a left join, IS NULL on a right-hand key finds the rows that matched nothing.
Example
SELECT name FROM employees WHERE manager_id IS NULL;Related terms
- NULL — SQL's marker for a missing or unknown value, which is not zero, not empty text and not equal to anything, even another NULL.
- anti join — A query that returns the rows of one table that have no matching row in another, such as products nobody has ordered.
- three-valued logic — SQL's rule that a condition can be true, false or unknown, where unknown comes from comparing with NULL.
Taught in
- Filtering With The WHERE Clause — PostgreSQL, Indexing & Relational Data Modeling
- What An Outer Join Really Returns — PostgreSQL, Indexing & Relational Data Modeling