NULLs in JOIN conditions in SQL

Introduction

Sometimes data is missing or unknown, shown as NULL. Understanding how NULLs affect JOINs helps you get the right combined data.

You want to combine two tables but some matching values might be missing.

You need to find all records including those without a matching pair.

You want to avoid losing data because NULLs don't match in JOINs.

You want to check which rows have missing related data.

You want to handle NULLs explicitly in your JOIN conditions.

Syntax

SQL

SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column;

NULL means unknown or missing value and does not equal anything, even another NULL.

JOIN conditions using '=' will not match rows where either side is NULL.

Examples

This JOIN matches rows where A.id equals B.id. Rows with NULL in either id will not match.

SQL

SELECT * FROM A
JOIN B ON A.id = B.id;

This keeps all rows from A, even if there is no matching B.id.

SQL

SELECT * FROM A
LEFT JOIN B ON A.id = B.id;

This JOIN treats NULLs as equal by explicitly checking for NULL in both columns.

SQL

SELECT * FROM A
JOIN B ON (A.id = B.id OR (A.id IS NULL AND B.id IS NULL));

Sample Program

This query joins tables A and B on id. Rows with NULL in id do not match, so Bob and Z are excluded.

SQL

CREATE TABLE A (id INT, name VARCHAR(10));
CREATE TABLE B (id INT, value VARCHAR(10));

INSERT INTO A VALUES (1, 'Alice'), (NULL, 'Bob'), (3, 'Carol');
INSERT INTO B VALUES (1, 'X'), (2, 'Y'), (NULL, 'Z');

-- Simple INNER JOIN
SELECT A.id, A.name, B.value
FROM A
JOIN B ON A.id = B.id;

Important Notes

NULLs never match with '=' in JOIN conditions.

Use LEFT JOIN to keep rows with NULLs on one side.

To treat NULLs as equal, use explicit IS NULL checks in the JOIN condition.

Summary

NULL means unknown and does not equal anything, even NULL.

JOINs using '=' skip rows with NULLs in join columns.

Use LEFT JOIN or explicit NULL checks to include NULLs in results.