How can you join tables and still include rows with no matches (using LEFT JOIN and IS NULL)?

Difficulty: 5/10

LEFT JOIN, NULL handling, anti-join

Using LEFT JOIN and IS NULL to Include Non-Matching Rows

To include rows from one table even when there are no matching rows in the related table, you can use a LEFT JOIN. A LEFT JOIN returns all rows from the left (primary) table and any matching rows from the right table. When no match exists, the right-side columns become NULL. By adding an IS NULL condition, you can specifically identify rows that do not have a match.

Example: Find customers who have never placed an order

In this query, all customers are selected, but only those with no corresponding orders (where orders.customer_id is NULL) are returned.

When to Use LEFT JOIN + IS NULL

1
To find rows in one table that have no related rows in another table
2
To identify missing or orphaned records
3
To perform anti-joins (show 'A' records that don't exist in 'B')
4
To ensure a complete list from the primary table even when data is missing on the joined side

Scenario Questions

0-2 years experience

Follow-up Questions