NULLs in JOIN conditions in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What happens when a NULL value is used in a JOIN condition?

In SQL, NULL represents an unknown value. When used in a JOIN condition, NULL does not match any value, including another NULL. This means rows with NULL in the join columns are usually excluded from the join result.

beginner

Why do NULLs cause rows to be excluded in INNER JOINs?

INNER JOIN returns rows where the join condition is true. Since NULL compared to any value (even NULL) is unknown (not true), rows with NULL in join columns do not satisfy the condition and are excluded.

intermediate

How can you include rows with NULLs in join columns when joining tables?

You can use LEFT JOIN or RIGHT JOIN to include rows with NULLs from one side. Also, you can use conditions like ON t1.col = t2.col OR (t1.col IS NULL AND t2.col IS NULL) to treat NULLs as equal in join conditions.

beginner

What is the difference between NULL and empty string ('') in JOIN conditions?

NULL means unknown or missing value, while empty string is a known value with zero length. In JOINs, NULL does not match anything, but empty string matches another empty string exactly.

intermediate

Write a JOIN condition that treats NULLs as equal in SQL.

Example:

SELECT * FROM table1 t1 JOIN table2 t2 ON (t1.col = t2.col OR (t1.col IS NULL AND t2.col IS NULL));

This condition matches rows where columns are equal or both NULL.

What does an INNER JOIN do with rows where the join column is NULL?

A Excludes them because NULL does not equal anything

B Converts NULL to zero and joins

C Includes them always

D Includes them if both sides have NULL

INNER JOIN excludes rows where the join condition is not true. Since NULL compared to anything is unknown (not true), those rows are excluded.

Which JOIN type can include rows with NULL in the join column from one table?

A INNER JOIN

B LEFT JOIN

C FULL JOIN

D CROSS JOIN

LEFT JOIN includes all rows from the left table, even if the join condition fails (including NULLs).

How can you write a join condition to treat NULLs as equal?

A Use <code>t1.col = t2.col</code> only

B Use <code>t1.col IS NULL OR t2.col IS NULL</code>

C Use <code>t1.col = t2.col OR (t1.col IS NULL AND t2.col IS NULL)</code>

D Use <code>t1.col != t2.col</code>

This condition explicitly matches rows where both columns are NULL, treating NULLs as equal.

What is the result of comparing NULL = NULL in SQL?

A UNKNOWN (treated as FALSE in JOIN)

B FALSE

C TRUE

D Error

NULL compared to NULL results in UNKNOWN, which is treated as FALSE in JOIN conditions.

Which of these is true about NULL in JOIN conditions?

A NULL is converted to zero automatically

B NULL matches NULL by default

C NULL matches empty string

D NULL never matches any value including NULL

NULL never matches any value, including another NULL, unless explicitly handled.

Explain why NULL values cause rows to be excluded in INNER JOINs and how to include them.

Think about how SQL treats NULL in comparisons and how different JOIN types behave.

Describe how to write a JOIN condition that treats NULL values as equal and why this might be useful.

Consider how NULLs are normally excluded and how to override that.