DocsJoinsLEFT JOIN

LEFT JOIN

Joins

Concept

LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matching rows from the right table. When a left-side row has no match on the right, the right-side columns are filled with NULL. This makes LEFT JOIN ideal for answering questions like "all customers and their orders, including customers who have never ordered".

The "left" and "right" refer to the order in which tables appear in the query: the table before LEFT JOIN is the left table, and the table after it is the right table. The left table is preserved in full; the right table contributes only matching rows.

LEFT JOIN is one of the most commonly used join types in reporting and analytics because it prevents data loss. When you INNER JOIN on a nullable foreign key, rows with NULL foreign keys disappear silently. LEFT JOIN keeps them visible, with NULL in the joined columns, so you can detect and handle missing relationships.

Syntax

-- Basic left join
SELECT c.customer_id, c.first_name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;

-- Finding rows with NO match (anti-join pattern)
SELECT c.customer_id, c.first_name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- All customers with their order count (including zero-order customers)
SELECT
  c.customer_id,
  c.first_name || ' ' || c.last_name AS customer,
  COUNT(o.order_id) AS order_count,
  COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC;

-- Products that have never been ordered
SELECT p.product_id, p.name, p.category
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
WHERE oi.order_item_id IS NULL
ORDER BY p.name;

Common Pitfalls & Tips

1When using LEFT JOIN with COUNT, count a column from the right table (e.g., COUNT(o.order_id)), not COUNT(*). COUNT(*) counts the row itself and will return 1 even for non-matching rows.
2Placing a filter on the right table in WHERE effectively converts a LEFT JOIN to an INNER JOIN. Move the condition to the ON clause instead to preserve all left-side rows.
3Be mindful of NULLs in aggregations after LEFT JOIN. Use COALESCE to convert NULLs to sensible defaults like 0.

Practice LEFT JOIN queries