Skip to content
Quantoros

What is a LEFT JOIN?

SQLLevel: FoundationJOINs Basics

What is a LEFT JOIN?

Hints
  • Write the join keys and relationship cardinality.
  • What happens to duplicates and unmatched rows? Check WHERE versus ON.

Answer

A LEFT JOIN returns all rows from the left table, and matching rows from the right table. Where there's no match, the right table's columns are NULL.

-- All customers, even those with no orders
SELECT c.customer_id, c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

"Left" means the table on the left side of the JOIN keyword is fully preserved.

Common follow-up: "How do you find customers with NO orders?" — filter for NULL on the right side:

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions