Skip to main content
Filtering & Logic
3 / 4
0%

Working with NULL

0/3 solved3 to unlock next
PrevLocked

Lesson 03

Working with NULL

Lesson Description

NULL is "unknown" — comparisons need IS NULL.

Easy Project

Mini project: Turn this lesson into a real-world query artifact by writing one clean business report query and validating output quality.

The value that is not a value

Sooner or later every analyst writes a filter that looks perfectly correct and returns the wrong rows. Nine times out of ten, the cause is NULL.

NULL is SQL's marker for "no value here". It can mean the value is unknown (we never recorded a patient's gender), missing (an office has no state), or not applicable (the CEO has no manager). Crucially, NULL is not zero, not an empty string and not false. It is the absence of a value, and SQL treats it with special rules.

A concrete question from the HR sandbox: "Which office locations have no state or province recorded?" The locations table has 10 offices. Only the four in the United States and Canada have a state; the six in Italy, the United Kingdom, Germany, the Netherlands and Mexico have NULL. Answering that question, and avoiding the traps around it, is what this lesson is about.

Syntax

WHERE column IS NULL
WHERE column IS NOT NULL

WHERE a IS DISTINCT FROM b        -- NULL-safe "not equal"
WHERE a IS NOT DISTINCT FROM b    -- NULL-safe "equal"

COALESCE(value1, value2, ...)     -- first non-NULL argument
NULLIF(a, b)                      -- NULL when a = b, otherwise a

Worked examples

Example 1: finding the missing values

SELECT city, country
FROM locations
WHERE state IS NULL;

This returns 6 rows: Roma, London, Oxford, Munich, Utrecht and Mexico City. Flip it to IS NOT NULL and you get the other 4 (Southlake, South San Francisco, Seattle, Toronto). Together they always add up to the full table, which is a useful sanity check.

The same idea works for the employee hierarchy:

SELECT first_name, last_name
FROM employees
WHERE manager_id IS NULL;

Exactly 1 row: Steven King, the president, who reports to nobody. The other 39 employees have a manager.

Example 2: why = NULL returns nothing

Here is what most people try first:

SELECT first_name, last_name
FROM employees
WHERE manager_id = NULL;

There is no error. It simply returns 0 rows, even though Steven King's manager_id is NULL. The reason: any comparison with NULL produces unknown, not true. You can see it directly:

SELECT NULL = NULL AS eq,
       NULL IS NULL AS is_null,
       1 + NULL AS plus,
       'abc' || NULL AS concat;

The result is one row where eq is NULL, is_null is true, plus is NULL and concat is NULL. Two unknown values are not known to be equal, so NULL = NULL is itself unknown. And arithmetic or string concatenation with NULL produces NULL: if you do not know one ingredient, you do not know the result.

Since WHERE keeps only rows where the condition is true, rows where it is unknown are dropped. IS NULL is the one test that asks the right question: "is this value missing?"

Example 3: NULLs hiding from <>

Business question: which offices are outside Texas?

SELECT city, state
FROM locations
WHERE state <> 'TX';

You would expect 9 rows. You get 3: South San Francisco, Seattle and Toronto. The six offices with no state are silently dropped, because NULL <> 'TX' is unknown.

There are two fixes. The explicit one:

SELECT city, state
FROM locations
WHERE state <> 'TX' OR state IS NULL;

Or the NULL-safe comparison operator:

SELECT city, state
FROM locations
WHERE state IS DISTINCT FROM 'TX';

Both return all 9 non-Texas offices. IS DISTINCT FROM treats NULL as an ordinary value for comparison purposes: NULL is distinct from 'TX', and NULL is not distinct from NULL. It never returns unknown.

Example 4: replacing NULL for display with COALESCE

Reports look better with something more helpful than a blank cell. COALESCE returns its first argument that is not NULL:

SELECT city, COALESCE(state, country) AS state_or_country
FROM locations
ORDER BY city;

All 10 rows come back. Seattle shows WA, while London shows United Kingdom because its state was NULL and COALESCE fell through to the country.

The arguments must have compatible types. manager_id is an integer, so to print a word instead you have to convert it to text first:

SELECT first_name, last_name,
       COALESCE(manager_id::text, 'none') AS manager
FROM employees
WHERE department_id = 90;

The Executive department has 3 rows: Steven King shows none, while Neena Kochhar and Lex De Haan show 100.

A related trick: || returns NULL if any piece is NULL, so city || ', ' || state gives NULL for Roma. The function concat_ws(', ', city, state) skips NULL arguments and returns plain Roma instead.

Example 5: counting what is missing

Aggregates are covered properly in the next course, but one detail belongs here. COUNT(*) counts rows; COUNT(column) counts non-NULL values in that column:

SELECT COUNT(*) AS all_rows,
       COUNT(state) AS with_state,
       COUNT(*) - COUNT(state) AS missing_state
FROM locations;

One row: 10, 4 and 6. This is a quick data-quality check you can run on any column. SUM, AVG, MIN and MAX also skip NULLs, so AVG over a column with gaps averages only the known values.

Under the hood: three-valued logic

Every condition in SQL evaluates to one of three values: true, false or unknown. WHERE, JOIN ... ON and HAVING all keep a row only when the result is true. (A CHECK constraint is the odd one out: it rejects a row only when the result is false, so unknown passes.)

ExpressionResult
NULL = 5, NULL <> 5, NULL > 5unknown
NULL = NULLunknown
NULL IS NULLtrue
NULL IS DISTINCT FROM 5true
true OR unknowntrue
false AND unknownfalse
NOT unknownunknown

The last row is why NOT cannot rescue you: negating unknown leaves it unknown.

NOT IN and NULL

The most famous NULL trap involves NOT IN. Since x NOT IN (a, b) means x <> a AND x <> b, a single NULL in the list poisons the result:

SELECT city, state
FROM locations
WHERE state NOT IN ('TX', NULL);

This returns 0 rows. For Seattle, 'WA' <> 'TX' is true but 'WA' <> NULL is unknown, and true AND unknown is unknown. No row can ever pass. Hard-coding NULL in a list looks silly, but the same thing happens when the list comes from a subquery whose column contains NULLs. You will meet that case with real data in the next lesson on EXISTS.

Sorting

PostgreSQL sorts NULLs as if they were larger than every other value: they come last in ascending order and first in descending order. ORDER BY state puts CA, ON, TX, WA and then six NULLs. You can override this with ORDER BY state NULLS FIRST or ORDER BY state DESC NULLS LAST.

Common mistakes

Mistake 1: = NULL or <> NULL. Both always produce unknown, so the query returns 0 rows with no error. Fix: IS NULL or IS NOT NULL.

Mistake 2: forgetting that <> and NOT skip NULLs. WHERE state <> 'TX' returns 3 rows, not 9. Fix: add OR state IS NULL, or use IS DISTINCT FROM.

Mistake 3: mixing types in COALESCE.

SELECT COALESCE(manager_id, 'none') FROM employees;

PostgreSQL tries to read 'none' as an integer and fails with invalid input syntax for type integer: "none". Fix: cast the column, COALESCE(manager_id::text, 'none').

Mistake 4: dividing by a column that might be zero. 100 / 0 raises division by zero. Wrapping the divisor in NULLIF(divisor, 0) turns a zero into NULL, so the result becomes NULL instead of an error: SELECT 100 / NULLIF(0, 0) returns NULL.

Other databases

  • IS NULL, IS NOT NULL, COALESCE and NULLIF are standard and work everywhere.
  • IS DISTINCT FROM is standard SQL, supported by PostgreSQL, SQLite (3.39+) and SQL Server 2022. MySQL uses its own NULL-safe equality operator <=> instead.
  • Sorting differs: MySQL and SQL Server treat NULL as the smallest value, so NULLs come first in ascending order, the opposite of PostgreSQL. SQLite also sorts NULLs first by default.
  • Oracle treats an empty string '' as NULL. PostgreSQL does not: '' is a real, zero-length string.

Practice

The exercises below this lesson ask you to:

  • Find HR employees who have no manager.
  • Find hospital patients whose gender has been recorded.
  • Find employees who have both a manager and a department, sorted by last name. This one combines two NULL tests with AND from the first lesson.

Key takeaways

  • NULL means "no value". It is not 0, not '' and not false.
  • Any comparison with NULL, including NULL = NULL, produces unknown, and WHERE drops unknown rows. Use IS NULL and IS NOT NULL.
  • <>, NOT and NOT IN silently skip NULL rows; IS DISTINCT FROM is the NULL-safe alternative.
  • COALESCE supplies a fallback value, NULLIF turns a specific value into NULL.
  • COUNT(*) counts rows, COUNT(column) counts non-NULL values.

Working with NULL: in depth

NULL means "unknown", and it changes how every comparison behaves. Missing department ids, empty end dates and optional fields are everywhere in real data, so knowing how NULL flows through filters, arithmetic and aggregates prevents numbers from quietly going wrong.

Worked examples

Finding and replacing missing values

SELECT first_name,
       COALESCE(department_id::text, 'Unassigned') AS department
FROM employees
WHERE department_id IS NULL;

IS NULL is the only reliable test for missing values. COALESCE returns the first non-NULL argument, which is useful for display labels.

NULLs in aggregates

SELECT COUNT(*) AS all_rows,
       COUNT(department_id) AS with_department
FROM employees;

COUNT(*) counts rows; COUNT(column) skips NULLs. The difference tells you how many employees lack a department.

Common mistakes

  • Arithmetic with NULL returns NULL: salary + NULL is NULL, so a missing bonus can blank out a whole total.
  • AVG ignores NULLs, which can overstate an average if missing values really mean zero. Wrap the column in COALESCE(col, 0) when that is the intent.
  • NULL = NULL is not true. Two rows with missing values do not match each other in a join or a WHERE.

Other databases

  • COALESCE is standard. SQL Server also has ISNULL(a, b), MySQL has IFNULL(a, b) and Oracle has NVL(a, b).
  • Oracle treats an empty string '' as NULL; PostgreSQL, MySQL and SQL Server treat them as different values.
Check yourself: Five employees exist and two have a NULL salary. What do COUNT(*), COUNT(salary) and AVG(salary) use?

COUNT(*) is 5, COUNT(salary) is 3, and AVG divides the sum of the three known salaries by 3, not by 5.

Concept check

Quick check: Working with NULL

3 quick questions. One at a time. Instant score at the end.

Exercise 1

Employees without a manager

Enterprise Workforce AnalyticseasyPostgreSQL

Return first_name and last_name of HR employees who have NO manager (manager_id is NULL).

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

Patients with recorded gender

Clinical Care PerformanceeasyPostgreSQL

From the hospital sandbox, return first_name and last_name of patients whose gender is recorded (not NULL).

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

Employees with both a manager and a department

Enterprise Workforce AnalyticseasyPostgreSQL

Return first_name and last_name of employees who have both a manager_id and a department_id (neither is NULL). Order by last_name.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Filtering & Logic
3 / 4
0%

Working with NULL

0/3 solved3 to unlock next
PrevLocked