NULLs in JOIN conditions in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of INNER JOIN with NULL values in join columns

Consider two tables Employees and Departments:

Employees:
id | name  | dept_id
1  | Alice | 10
2  | Bob   | NULL
3  | Carol | 20

Departments:
dept_id | dept_name
10      | Sales
20      | HR
NULL    | Unknown

What will be the result of this query?

SELECT e.name, d.dept_name
FROM Employees e
INNER JOIN Departments d ON e.dept_id = d.dept_id;

SQL

SELECT e.name, d.dept_name
FROM Employees e
INNER JOIN Departments d ON e.dept_id = d.dept_id;

A [{"name": "Alice", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "HR"}]

B [{"name": "Alice", "dept_name": "Sales"}, {"name": "Bob", "dept_name": "Unknown"}, {"name": "Carol", "dept_name": "HR"}]

C [{"name": "Bob", "dept_name": "Unknown"}]

D []

💡 Hint

Remember that = comparison with NULL does not return true in SQL.

In SQL, NULL = NULL is not true, so rows with NULL in join columns do not match in INNER JOIN. Only rows with matching non-NULL values join.

❓ query_result

intermediate

LEFT JOIN behavior with NULLs in join columns

Using the same tables Employees and Departments as before, what is the output of this query?

SELECT e.name, d.dept_name
FROM Employees e
LEFT JOIN Departments d ON e.dept_id = d.dept_id;

SQL

SELECT e.name, d.dept_name
FROM Employees e
LEFT JOIN Departments d ON e.dept_id = d.dept_id;

A [{"name": "Alice", "dept_name": "Sales"}, {"name": "Bob", "dept_name": null}, {"name": "Carol", "dept_name": "HR"}]

B [{"name": "Alice", "dept_name": "Sales"}, {"name": "Bob", "dept_name": "Unknown"}, {"name": "Carol", "dept_name": "HR"}]

C [{"name": "Bob", "dept_name": null}]

D []

💡 Hint

LEFT JOIN keeps all rows from the left table, even if no match is found on the right.

Bob's dept_id is NULL, so no matching department row is found. LEFT JOIN returns Bob's row with dept_name as NULL.

🧠 Conceptual

advanced

Why does NULL in join condition cause no match?

Why does a join condition like table1.col = table2.col fail to match rows when either column contains NULL?

A Because NULL is treated as a special number that matches only itself.

B Because NULL is treated as zero, so it only matches zero values.

C Because NULL is treated as an empty string, so it only matches empty strings.

D Because NULL is treated as an unknown value, and comparisons with unknown always return false in SQL.

💡 Hint

Think about how SQL treats NULL in logical comparisons.

In SQL, NULL means unknown. Any comparison with unknown results in unknown, which is treated as false in WHERE or JOIN conditions.

📝 Syntax

advanced

Correct syntax to join on NULL-safe equality

Which SQL join condition correctly matches rows where columns may be NULL, treating NULLs as equal?

A ON e.dept_id <> d.dept_id

B ON e.dept_id = d.dept_id AND e.dept_id IS NOT NULL

C ON e.dept_id = d.dept_id OR (e.dept_id IS NULL AND d.dept_id IS NULL)

D ON e.dept_id IS NULL AND d.dept_id IS NULL

💡 Hint

Think about how to explicitly check for NULL equality in SQL.

Since = does not treat NULLs as equal, adding OR (col1 IS NULL AND col2 IS NULL) handles NULL-safe equality.

❓ optimization

expert

Optimizing JOIN with NULL-safe condition for large tables

You have two large tables with millions of rows each, and you want to join them on columns that may contain NULLs, treating NULLs as equal. Which approach is most efficient?

A Use CROSS JOIN and filter matching rows with NULL-safe condition in WHERE clause.

B Use <code>ON COALESCE(t1.col, -1) = COALESCE(t2.col, -1)</code> assuming -1 is not a valid value.

C Use a FULL OUTER JOIN and filter rows with matching or both NULL columns in WHERE clause.

D Use <code>ON t1.col = t2.col OR (t1.col IS NULL AND t2.col IS NULL)</code> with proper indexes on the columns.

💡 Hint

Consider how indexes and functions affect query performance.

Using COALESCE allows the join to use indexes if the columns are indexed, improving performance over OR conditions which often disable index use.