SQL LEFT OUTER JOIN Explained

Source: DataDriven analysis of 1,042 verified data engineering interview rounds.

LEFT JOIN Syntax

LEFT JOIN and LEFT OUTER JOIN are byte-for-byte identical to the parser. The OUTER keyword is cosmetic. Most teams drop it because five characters aren't worth the debate in code review. The real action is in the ON clause, which is where the interviewer is going to set a trap.

-- These two are identical
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id;
SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.a_id;

-- Full syntax
SELECT
  left_table.column1,
  right_table.column2
FROM left_table
LEFT JOIN right_table
  ON left_table.key = right_table.key
  [AND additional_conditions]
WHERE [post-join_filters];

LEFT JOIN vs INNER JOIN

The difference only matters when unmatched rows exist. If every left-table row has at least one match, both join types return the same result. The divergence appears when some left rows have no corresponding right rows.

-- customers: Alice (id=1), Bob (id=2), Carol (id=3)
-- orders: order for Alice (customer_id=1), order for Bob (customer_id=2)

-- LEFT JOIN: 3 rows (Carol has NULLs for order columns)
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
-- Alice  | 101
-- Bob    | 102
-- Carol  | NULL    <-- preserved by LEFT JOIN

-- INNER JOIN: 2 rows (Carol dropped)
SELECT c.name, o.order_id
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
-- Alice  | 101
-- Bob    | 102

5 LEFT JOIN Patterns

From basic entity preservation to the anti-join and the critical ON vs WHERE distinction. These patterns cover the full range of LEFT JOIN usage in interviews and production.

-- All customers, with their orders if any
SELECT
  c.customer_id,
  c.name,
  o.order_id,
  o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- Customers with no orders will have:
-- order_id = NULL, amount = NULL
-- They are NOT dropped from the result

-- Customers who have never placed an order
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

-- Alternative: NOT EXISTS (often same performance)
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
);

-- Alternative: NOT IN (careful with NULLs)
SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

-- All departments with their employee count and total salary
SELECT
  d.department_name,
  COUNT(e.employee_id) AS headcount,
  COALESCE(SUM(e.salary), 0) AS total_payroll
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

-- Departments with no employees show headcount = 0, total_payroll = 0
-- COUNT(e.employee_id) correctly returns 0 for NULLs
-- SUM(e.salary) returns NULL for empty groups, so COALESCE is needed

-- Customer profile with latest order and support ticket count
SELECT
  c.customer_id,
  c.name,
  c.email,
  lo.latest_order_date,
  lo.latest_amount,
  COALESCE(t.ticket_count, 0) AS tickets
FROM customers c
LEFT JOIN (
  SELECT DISTINCT ON (customer_id)
    customer_id,
    order_date AS latest_order_date,
    amount AS latest_amount
  FROM orders
  ORDER BY customer_id, order_date DESC
) lo ON c.customer_id = lo.customer_id
LEFT JOIN (
  SELECT customer_id, COUNT(*) AS ticket_count
  FROM support_tickets
  GROUP BY customer_id
) t ON c.customer_id = t.customer_id;

-- CORRECT: filter in ON clause (preserves unmatched customers)
SELECT c.customer_id, c.name, o.order_id
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
  AND o.status = 'completed';
-- Customers with no completed orders: order_id = NULL (preserved)

-- WRONG: filter in WHERE clause (drops unmatched customers)
SELECT c.customer_id, c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'completed';
-- Customers with no completed orders are DROPPED
-- because o.status is NULL for them, and NULL != 'completed'

Common LEFT JOIN Mistakes

These three mistakes produce queries that run successfully but return incorrect results. Each one has cost real candidates real offers.

4 LEFT JOIN Interview Questions

These questions test join mechanics, anti-join patterns, fan-out understanding, and the ON vs WHERE distinction.

LEFT JOIN FAQ

Related Guides