LEFT JOIN

LEFT JOIN keeps every row from the left table.

When there is no match on the right side, right-table columns are NULL.

Core Concepts

flowchart LR
  A[Left rows] --> B{Right match exists?}
  B -->|Yes| C[Return combined row]
  B -->|No| D[Return left row + NULL right columns]

Behavior	Result
Matched row	Left + right values
Unmatched left row	Left values + NULL on right

Code Examples

-- Add a customer with no orders.
INSERT INTO customers_join (customer_id, name)
VALUES (3, 'Grace');

Expected output
One new customer inserted (Grace).

-- Keep all customers, including those with no orders.
SELECT
  c.customer_id,
  c.name,
  o.order_id,
  o.total
FROM customers_join AS c
LEFT JOIN orders_join AS o
  ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id;

customer_id	name	order_id	total
1	Ada	101	49.99
1	Ada	102	19.50
2	Alan	103	75.00
3	Grace	null	null

-- Find customers without orders.
SELECT c.customer_id, c.name
FROM customers_join AS c
LEFT JOIN orders_join AS o
  ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL
ORDER BY c.customer_id;

customer_id	name
3	Grace

SQLite-Specific Nuances

SQLite Nuance

SQLite supports LEFT JOIN and the equivalent LEFT OUTER JOIN syntax.

Common Pitfalls / Best Practices

Pitfall

Adding WHERE filters on right-table columns without null-awareness, unintentionally removing unmatched left rows.

Best Practice

When looking for "missing matches," use LEFT JOIN plus WHERE right_key IS NULL.

Quick Challenge

List all customers with a derived column has_order (1 if matched, 0 if not).

View Solution

SELECT
  c.customer_id,
  c.name,
  CASE WHEN o.order_id IS NULL THEN 0 ELSE 1 END AS has_order
FROM customers_join AS c
LEFT JOIN orders_join AS o
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

customer_id	name	has_order
1	Ada	1
2	Alan	1
3	Grace	0