SQL Joins and Subqueries

SQL Joins

A join lines up rows from two tables using a shared key, so you can see, for example, each order next to the customer's name.

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

o and c are table aliases, short nicknames that save typing. ON states which columns must match.

The main types (picture two overlapping circles):

• INNER JOIN: only rows that match in both tables. Customers with no orders disappear.
• LEFT JOIN: every row from the left (first) table, plus matches from the right. Where there is no match, the right-hand columns are NULL. Use it to find customers who have never ordered: LEFT JOIN orders ... WHERE o.order_id IS NULL.
• RIGHT JOIN: the mirror image of LEFT JOIN. Most analysts just swap the table order and use LEFT.
• FULL OUTER JOIN: all rows from both tables, matched where possible. (MySQL does not support it directly.)

Joining three tables is just two joins in a row:

SELECT c.name, p.product_name, o.quantity, o.quantity * p.price AS revenue
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products  p ON o.product_id  = p.product_id;

Check your joins: count rows before and after. If the count jumps unexpectedly, you probably joined on the wrong column.

Subqueries

A subquery is a query inside another query, written in parentheses. SQL runs the inner query first and uses its answer in the outer query. Use one when a question has two steps.

In WHERE: "Which products cost more than the average price?" Step 1: find the average. Step 2: compare.

SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

With IN: "Which customers bought something in the Books category?"

SELECT name FROM customers
WHERE customer_id IN (
  SELECT o.customer_id
  FROM orders o JOIN products p ON o.product_id = p.product_id
  WHERE p.category = 'Books');

In FROM: treat a summarized result as a temporary table, then query it. "What is the average number of orders per customer?"

SELECT AVG(order_count)
FROM (SELECT customer_id, COUNT(*) AS order_count
      FROM orders GROUP BY customer_id) AS per_customer;

Tip: write and run the inner query on its own first. When it returns what you expect, wrap it inside the outer query.