NULL and Three-Valued Logic

NULL means "unknown or missing". It is not zero and not an empty string. Any comparison with an unknown value is unknown, so SQL has three truth values: TRUE, FALSE, and UNKNOWN. WHERE keeps only rows where the condition is TRUE.

Example 1: comparing with NULL

Tara has no department, so dept_id is NULL.

SELECT name FROM employees WHERE dept_id = NULL;

 name 
------
(0 rows)

No rows. dept_id = NULL is UNKNOWN, never TRUE. Use IS NULL.

SELECT name FROM employees WHERE dept_id IS NULL;

 name 
------
 Tara
(1 row)

Example 2: NULL = NULL and NULL <> NULL

psql prints NULL as an empty cell, so a blank result below means NULL.

SELECT NULL = NULL AS eq, NULL <> NULL AS ne, NULL IS NULL AS is_null;

 eq | ne | is_null 
----+----+---------
    |    | t
(1 row)

Example 3: a filter silently drops NULL rows

SELECT count(*) FROM employees WHERE dept_id <> 1;

 count 
-------
     6
(1 row)

There are 12 employees, 5 in department 1, and 6 elsewhere. Tara is in neither list because NULL <> 1 is UNKNOWN. To include her:

SELECT count(*) FROM employees WHERE dept_id IS DISTINCT FROM 1;

 count 
-------
     7
(1 row)

IS DISTINCT FROM treats NULL as an ordinary value. It is standard SQL.

Example 4: three-valued AND and OR

SELECT (TRUE OR NULL) AS t_or_null, (FALSE AND NULL) AS f_and_null, (TRUE AND NULL) AS t_and_null;

 t_or_null | f_and_null | t_and_null 
-----------+------------+------------
 t         | f          | 
(1 row)

Example 5: COALESCE gives a default

COALESCE returns its first non-NULL argument.

SELECT name, COALESCE(dept_id::text, 'none') AS dept
FROM employees
WHERE id IN (11, 12);

 name  | dept 
-------+------
 Rohan | 4
 Tara  | none
(2 rows)

Example 6: NULLIF avoids divide by zero

NULLIF(a, b) returns NULL when a = b, otherwise a.

SELECT 100 / NULLIF(0, 0) AS safe;

 safe 
------
     
(1 row)

Example 7: aggregates skip NULL

count(*) counts rows. count(column) counts non-NULL values.

SELECT count(*) AS all_rows,
       count(dept_id) AS with_dept,
       count(manager_id) AS with_manager
FROM employees;

 all_rows | with_dept | with_manager 
----------+-----------+--------------
       12 |        11 |           11
(1 row)

avg, sum, min, and max also ignore NULL. sum over zero rows, or only NULLs, is NULL, not 0.

SELECT sum(salary) AS total FROM employees WHERE dept_id = 5;

 total 
-------
      
(1 row)

SELECT COALESCE(sum(salary), 0) AS total FROM employees WHERE dept_id = 5;

 total 
-------
     0
(1 row)

Example 8: NOT IN with NULL returns nothing

This is a classic trap. Find departments that have no employees:

SELECT name FROM departments
WHERE id NOT IN (SELECT dept_id FROM employees);

 name 
------
(0 rows)

No rows, although Legal has no employees. The subquery returns a NULL (Tara). id NOT IN (1, 2, 3, 4, NULL) becomes id <> 1 AND ... AND id <> NULL, which is never TRUE.

Two safe fixes:

SELECT name FROM departments
WHERE id NOT IN (SELECT dept_id FROM employees WHERE dept_id IS NOT NULL);

 name  
-------
 Legal
(1 row)

SELECT d.name FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

 name  
-------
 Legal
(1 row)

Prefer NOT EXISTS.

Example 9: sorting NULLs

PostgreSQL sorts NULL as larger than any value: last in ASC, first in DESC. Control it explicitly with NULLS FIRST or NULLS LAST.

SELECT name, dept_id FROM employees ORDER BY dept_id NULLS FIRST, id LIMIT 3;

 name  | dept_id 
-------+---------
 Tara  |        
 Meera |       1
 Ravi  |       1
(3 rows)

Example 10: NULLs form one group

DISTINCT and GROUP BY treat all NULLs as one group, even though NULL = NULL is not true. (A UNIQUE constraint in PostgreSQL, by contrast, allows several NULLs.)

SELECT dept_id, count(*) FROM employees GROUP BY dept_id ORDER BY dept_id;

 dept_id | count 
---------+-------
       1 |     5
       2 |     3
       3 |     2
       4 |     1
         |     1
(5 rows)

Summary

• Test with IS NULL and IS NOT NULL, never = NULL.
• WHERE keeps only TRUE. UNKNOWN rows are dropped.
• NOT IN plus a NULL in the list returns no rows. Use NOT EXISTS.
• COALESCE supplies defaults, NULLIF produces NULL on purpose.