NULLs in INNER and LEFT JOINs

Concepts covered: Nulls in Joins

How a join pairs rows

A join reads 2 tables and pairs their rows. FROM orders o INNER JOIN customers c names the tables o and c for the rest of the query, and the ON clause gives the condition a pair has to satisfy. o.customer_id = c.customer_id is TRUE when both sides hold the same value, FALSE when they differ, and NULL when either side is NULL, because a comparison with NULL has no answer. The join keeps pairs whose condition is TRUE and nothing else. A NULL key therefore pairs with no row at all, including a NULL key on the other side: NULL = NULL is NULL, never TRUE.

An INNER JOIN returns the pairs and nothing more. A LEFT JOIN returns the pairs, then adds back every row of the left table that found no partner, with the right table's columns filled with NULL for those rows.

INNER JOIN drops the row

SELECT o.order_id, o.customer_id, c.name
FROM orders o
INNER JOIN customers c
  ON o.customer_id = c.customer_id

The 4 orders meet 4 customers and 2 rows come out. ORD-1 and ORD-2 find Alice and Bob. The NULL customer_id on ORD-3 makes every comparison NULL, so it pairs with nobody, including the Guest customer whose key is also NULL. ORD-4 carries C-103, a key no customer owns, so every comparison is FALSE. The 2 result rows show no trace of either loss.

The orders table has 100 rows; 10 have a NULL customer_id and each of the other 90 holds a key that appears exactly once in customers. The customers table has 50 rows, 1 of them with a NULL customer_id. How many rows does this query return?

SELECT COUNT(*)
FROM orders o
INNER JOIN customers c
  ON o.customer_id = c.customer_id

• 100, because the 10 orders with a NULL key pair with the single customer whose key is also NULL
• 50, because an INNER JOIN returns 1 row for each row of the smaller table
• 90, because the 10 orders with a NULL key produce no pair and each of the other 90 matches exactly 1 customer
• 99, because 9 of the NULL orders fail and 1 of them pairs with the NULL customer

LEFT JOIN keeps the row and marks the gap

SELECT e.emp_id, e.dept_id, d.dept_name
FROM employees e
LEFT JOIN departments d
  ON e.dept_id = d.dept_id

The 3 employees go in and 3 rows come out. E-101 pairs with Sales. The NULL dept_id on E-103 never pairs with the NULL key of the Unassigned department, and E-105 points at D-99, which no department owns. Both survive with dept_name NULL, so the gap shows up where the match would have been.

Telling the 2 NULLs apart

The right side's NULL says 'no partner found'. Whether the left key was itself missing, or present and unmatched, is a question for the left table's own column, and a CASE expression can ask both questions at once.

SELECT
  e.emp_id,
  e.dept_id,
  d.dept_name,
  CASE
    WHEN e.dept_id IS NULL THEN 'no department recorded'
    WHEN d.dept_id IS NULL THEN 'department not found'
    ELSE 'matched'
  END AS match_status
FROM employees e
LEFT JOIN departments d
  ON e.dept_id = d.dept_id

The CASE tests d.dept_id, the join key, in place of d.dept_name. A department row with a key but a NULL name would still be a match, and testing the name would label it unmatched. Test the key the ON clause compared, which every matched row carries.

The filter that undoes a LEFT JOIN

A LEFT JOIN protects its rows only until WHERE runs. WHERE keeps rows whose condition is TRUE, and every comparison against a NULL the join filled in is NULL. Ask for every employee outside Sales, then add the branch that keeps the gaps.

Filter only

The comparison alone: every row whose dept_name is NULL evaluates to NULL and is discarded.

SELECT
  e.emp_id,
  d.dept_name
FROM employees e
LEFT JOIN departments d
  ON e.dept_id = d.dept_id
WHERE d.dept_name != 'Sales'

With IS NULL

An IS NULL branch keeps the employees the LEFT JOIN preserved, so the answer to 'outside Sales' is complete.

SELECT
  e.emp_id,
  d.dept_name
FROM employees e
LEFT JOIN departments d
  ON e.dept_id = d.dept_id
WHERE d.dept_name != 'Sales'
  OR d.dept_name IS NULL

The first tab returns no rows. E-101 is in Sales and fails the test as it should. E-103 and E-105 have a NULL dept_name, so NULL != 'Sales' is NULL and WHERE discards them: the LEFT JOIN kept them and the next clause threw them out. The second tab's OR d.dept_name IS NULL brings both back.

Counting after a join

After a LEFT JOIN, COUNT(*) counts every left row and COUNT of a right-side key counts only the matched ones. The difference is the number of rows an INNER JOIN would have erased without a trace.

SELECT
  COUNT(*) AS employees,
  COUNT(d.dept_id) AS with_department,
  COUNT(*) - COUNT(d.dept_id) AS without_department
FROM employees e
LEFT JOIN departments d
  ON e.dept_id = d.dept_id

With INNER JOIN in place of LEFT JOIN the same query returns 1, 1 and 0. The 2 unmatched employees are counted nowhere, and nothing in the result shows they exist.

Each row of transactions carries a merchant_id that is NULL when the card terminal sent none. Keep every transaction and label it 'no merchant id', 'merchant not found' or 'matched'.

About this lesson

This section is part of the NULL Values: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.