Joining Tables in SQL

Why JOINs Matter

Real-world data is spread across multiple tables. A customer's name is in the customers table. Their orders are in the orders table. The products in each order are in the order_items table. Product details are in the products table.

JOINs allow you to combine these tables into a single result set by matching rows based on a shared column (usually a primary key and foreign key pair).

Types of Joins

INNER JOIN

Returns only rows where there is a match in BOTH tables.

SELECT
  o.order_id,
  o.order_date,
  c.name AS customer_name,
  c.country
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

If an order has a customer_id that does not exist in the customers table, or a customer has no orders, those rows are excluded.

LEFT JOIN (LEFT OUTER JOIN)

Returns ALL rows from the left table, plus matching rows from the right table. Non-matching right rows are filled with NULL.

-- All customers, including those with no orders
SELECT
  c.customer_id,
  c.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.name
ORDER BY order_count DESC;

Customers with no orders will appear with order_count = 0.

RIGHT JOIN (RIGHT OUTER JOIN)

Returns ALL rows from the right table, plus matching rows from the left. Less commonly used -- a RIGHT JOIN can always be rewritten as a LEFT JOIN by swapping the table order.

FULL OUTER JOIN

Returns all rows from both tables, with NULLs where there is no match in either direction.

SELECT
  c.customer_id,
  o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

CROSS JOIN

Returns the cartesian product: every row in the first table paired with every row in the second table. Used rarely but useful for generating all possible combinations.

Table Aliases

Always use table aliases when joining multiple tables. They make queries much more readable:

-- Without aliases (verbose and repetitive)
SELECT orders.order_id, customers.name, products.product_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN order_items ON orders.order_id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.product_id;

-- With aliases (clean and readable)
SELECT o.order_id, c.name, p.product_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id;

Joining Three or More Tables

Chain multiple JOINs to combine many tables:

-- Complete order details: order, customer, product, category
SELECT
  o.order_id,
  o.order_date,
  c.name AS customer_name,
  c.country,
  p.product_name,
  cat.category_name,
  oi.quantity,
  oi.unit_price,
  oi.quantity * oi.unit_price AS line_total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN categories cat ON p.category_id = cat.category_id
WHERE o.order_date >= '2024-01-01'
ORDER BY o.order_date DESC, o.order_id;

Finding Non-Matching Rows

Use LEFT JOIN with WHERE IS NULL to find rows in one table that have no match in another:

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

The LEFT JOIN includes all products. For products with no orders, oi.product_id will be NULL. WHERE oi.product_id IS NULL filters to only these unordered products.

Self Join

A self join joins a table to itself. Useful for hierarchical or comparative data:

-- Find employees and their managers from the same employees table
SELECT
  e.employee_id,
  e.name AS employee_name,
  m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Common Join Mistakes

Forgetting the ON condition (Cartesian product):

-- WRONG: Creates a cartesian product of all rows
SELECT * FROM orders, customers;

-- CORRECT: Specify the join condition
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

Ambiguous column names:

-- WRONG: Both tables have 'customer_id' -- which one?
SELECT customer_id FROM orders JOIN customers ON orders.customer_id = customers.customer_id;

-- CORRECT: Qualify with table alias
SELECT o.customer_id FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

Using INNER JOIN when you need LEFT JOIN: If you use INNER JOIN and some customers have no orders, those customers are excluded from the result. If the question asks about ALL customers (including those without orders), you need LEFT JOIN.

When to Use Each Join Type

Key Takeaways

• INNER JOIN returns only matching rows from both tables; LEFT JOIN returns all rows from the left table plus matching rows from the right.
• Always use table aliases when joining multiple tables to avoid verbose and ambiguous column references.
• Finding non-matching rows: LEFT JOIN + WHERE right_table.key IS NULL finds all left-table rows with no match in the right table.
• Always specify the ON condition -- missing it creates a cartesian product (every row paired with every other row).
• When the question asks about ALL records from a table (including those with no related records), use LEFT JOIN, not INNER JOIN.

Practice Exercise

Write SQL queries to answer these questions using JOIN:

1. List all orders with customer name, country, and order amount
2. Find all customers who have NEVER placed an order (hint: LEFT JOIN + WHERE IS NULL)
3. Calculate total revenue per product category, joining orders > order_items > products > categories
4. Find the top 5 customers by total lifetime spend, showing their name and total amount
5. List all products that have been ordered in 2024 with the customer name for each order