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?
DfLEFT JOIN
A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matching rows from the right table. When no match is found, NULL values are returned for columns from the right table.
-- 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;
Use LEFT JOIN with IS NULL on the right table to find records that don't have related data β a common pattern for finding orphaned or unmatched records.
LEFT JOIN vs INNER JOIN
DfJoin Comparison
INNER JOIN returns only rows with matches in both tables. LEFT JOIN returns all left table rows, adding NULLs for unmatched right table rows.
| 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';
Be cautious with LEFT JOINs followed by WHERE conditions on the right table β this can effectively convert it to an INNER JOIN.
Key Takeaways
- LEFT JOIN returns all rows from the left table regardless of matches
- Unmatched rows show NULL for right table columns
- Use IS NULL on right table columns to find unmatched records
- LEFT JOIN is essential for finding orphaned or missing related data
- Avoid WHERE clauses on right table columns after LEFT JOIN