Finding unmatched rows with LEFT JOIN in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does a LEFT JOIN do in SQL?

A LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there is no match, the result is NULL on the right side.

beginner

How can you find rows in the left table that have no matching rows in the right table using LEFT JOIN?

Use a LEFT JOIN and then filter where the right table's key column is NULL. This shows unmatched rows from the left table.

beginner

Why do we check for NULL in the right table's columns after a LEFT JOIN?

Because unmatched rows from the left table will have NULL values in the right table's columns, indicating no match was found.

intermediate

Write a simple SQL query to find customers who have no orders using LEFT JOIN.

SELECT customers.* FROM customers LEFT JOIN orders ON customers.id = orders.customer_id WHERE orders.customer_id IS NULL;

intermediate

What is the difference between LEFT JOIN and INNER JOIN when finding unmatched rows?

INNER JOIN returns only matching rows from both tables, so it excludes unmatched rows. LEFT JOIN returns all left table rows including unmatched ones.

What does the condition 'WHERE right_table.id IS NULL' do after a LEFT JOIN?

A Finds rows in the left table with no match in the right table

B Finds rows in the right table only

C Finds rows with matching keys in both tables

D Deletes unmatched rows

Checking for NULL in the right table's key column after a LEFT JOIN identifies rows in the left table that have no matching row in the right table.

Which JOIN type returns all rows from the left table regardless of matches?

A LEFT JOIN

B RIGHT JOIN

C FULL JOIN

D INNER JOIN

LEFT JOIN returns all rows from the left table and matched rows from the right table.

If you want to find unmatched rows in the right table, which JOIN would you use?

A LEFT JOIN

B RIGHT JOIN

C INNER JOIN

D CROSS JOIN

RIGHT JOIN returns all rows from the right table and matched rows from the left table. Checking for NULL in the left table columns finds unmatched right table rows.

What happens to unmatched rows from the right table in a LEFT JOIN?

A They are duplicated

B They are included with NULLs in left table columns

C They cause an error

D They are excluded from the result

LEFT JOIN includes all left table rows. Unmatched right table rows are excluded.

Which SQL clause is essential to filter unmatched rows after a LEFT JOIN?

A GROUP BY

B HAVING

C WHERE right_table.column IS NULL

D ORDER BY

Filtering with WHERE right_table.column IS NULL selects rows from the left table without matches in the right table.

Explain how to find unmatched rows in one table using LEFT JOIN.

Think about what happens to unmatched rows in a LEFT JOIN result.

Describe the difference between INNER JOIN and LEFT JOIN when looking for unmatched rows.

Consider which join keeps unmatched rows from the left table.