SQL Joins

LEFT JOIN for Left Outer Queries

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

All Left Rows — includes every record from the primary table
Optional Right Data — NULLs fill where no match exists Never miss records from your primary table.

What is a LEFT JOIN?

-- LEFT JOIN returns all customers, even without orders
SELECT 
  c.customer_name,
  o.order_id,
  o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- LEFT JOIN with multiple tables
SELECT 
  c.customer_name,
  o.order_id,
  p.product_name,
  oi.quantity
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id;

LEFT JOIN Behavior

Customers Table	Orders Table	LEFT JOIN Result
ID=1, John	OrderID=101, CustID=1	ID=1, John, Order 101
ID=2, Jane	OrderID=102, CustID=2	ID=2, Jane, Order 102
ID=3, Bob	No orders	ID=3, Bob, NULL
ID=4, Alice	No orders	ID=4, Alice, NULL

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

-- Count orders per customer (including zero orders)
SELECT 
  c.customer_name,
  COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

LEFT JOIN vs INNER JOIN

Scenario	INNER JOIN	LEFT JOIN
All customers with orders	Returns customers with orders	Returns all customers
Customers without orders	Excluded	Included with NULLs
Count per customer	Only customers with orders	All customers
NULL handling needed	Not needed	Required

-- Find all employees and their department (including unassigned)
SELECT 
  e.employee_name,
  COALESCE(d.department_name, 'Unassigned') AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- Products without any sales
SELECT 
  p.product_name,
  COALESCE(SUM(oi.quantity), 0) AS total_sold
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name
HAVING COALESCE(SUM(oi.quantity), 0) = 0;

Common LEFT JOIN Patterns

-- LEFT JOIN with aggregate functions
SELECT 
  c.customer_name,
  MAX(o.order_date) AS last_order_date,
  COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

-- LEFT JOIN for reporting
SELECT 
  d.department_name,
  COUNT(e.employee_id) AS headcount,
  COALESCE(AVG(e.salary), 0) AS avg_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

-- Multiple LEFT JOINs with filtering
SELECT 
  c.customer_name,
  o.order_id,
  o.order_date,
  p.product_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id
WHERE c.country = 'USA';

LEFT JOIN for Left Outer Queries

LEFT JOIN for Left Outer Queries

What is a LEFT JOIN?

LEFT JOIN Behavior

LEFT JOIN vs INNER JOIN

Common LEFT JOIN Patterns

Need Expert SQL Help?