Lab 06 — JOINs: Combining Tables

The most powerful concept in SQL. Link customers, orders, and products together into one result set.

⏳ Loading SQL engine...

📋 AVAILABLE TABLES: products, customers, ordersproducts(id, name, category, price, stock) customers(id, name, city, email, member_since) orders(id, customer_id, product_id, quantity, order_date, total) Key relationships: orders.customer_id --> customers.id orders.product_id --> products.id

📖 Concept Recap: JOINs

INNER JOIN returns only rows where a match exists in both tables.
LEFT JOIN returns all rows from the left table plus matched rows from the right. Unmatched right-side columns are NULL.
ON specifies the join condition: ON orders.customer_id = customers.id
Use table aliases (o, p, c) to keep queries readable: FROM orders o
Prefix ambiguous columns with the alias: o.id vs c.name vs p.name
You can chain multiple JOIN clauses to link three or more tables.

👀 Worked Example (read-only)

Full order history joining all three tables at once.

SELECT o.id AS order_id,
       c.name AS customer,
       c.city,
       p.name AS product,
       p.category,
       o.quantity,
       o.total,
       o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p ON o.product_id = p.id
ORDER BY o.order_date;

✏ Guided

Exercise 1 — Basic INNER JOIN

Show each order with the customer name and product name. Fill in the blanks: the correct column names and JOIN conditions.

💡 Hint: o = orders alias, c = customers alias, p = products alias. The linking columns are customer_id and product_id.

SELECT o.id AS order_id,
       c.___ AS customer_name,
       p.___ AS product_name,
       o.quantity,
       o.total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.___
INNER JOIN products p ON o.product_id = p.___
ORDER BY o.id;

💪 Independent

Exercise 2 — LEFT JOIN: All Customers Including Those with No Orders

Show all customers with their total amount spent. Customers who have never placed an order should appear with 0 total (use COALESCE). Sort by total spent descending.

💡 Hint: Start FROM customers c LEFT JOIN orders o ON c.id = o.customer_id, then GROUP BY and use COALESCE(SUM(o.total), 0).

SELECT c.id,
       c.name,
       c.city,
       c.member_since,
       COUNT(o.id) AS order_count,
       COALESCE(ROUND(SUM(o.total), 2), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.city, c.member_since
ORDER BY total_spent DESC;

🔥 Challenge

Exercise 3 — Products That Have Never Been Ordered

Use a LEFT JOIN from products to orders, then filter WHERE orders.id IS NULL to find products with no order history. These are dead-stock items.

💡 Hint: FROM products p LEFT JOIN orders o ON p.id = o.product_id WHERE o.id IS NULL

-- Products that have NEVER been ordered (dead stock)
SELECT p.id,
       p.name AS product_name,
       p.category,
       p.price,
       p.stock AS units_in_stock
FROM products p
LEFT JOIN orders o ON p.id = o.product_id
WHERE o.id IS NULL
ORDER BY p.category, p.name;

🏆 Mini Project

Mini Project — Complete Order Intelligence Report

As a business analyst, write 4 JOIN queries to understand purchasing behaviour fully:

Full order history: order ID, customer name, product name, category, quantity, total, date.
Customer purchase summary: name, city, number of orders, total spent — sorted by total DESC.
Product popularity: product name, category, times ordered, total quantity sold.
Which customers bought which categories: customer name, category, total spent in that category.

-- 1. Full order history
SELECT o.id, c.name AS customer, p.name AS product,
       p.category, o.quantity, o.total, o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p ON o.product_id = p.id
ORDER BY o.order_date;

-- 2. Customer purchase summary
SELECT c.name, c.city,
       COUNT(o.id) AS orders,
       ROUND(SUM(o.total), 2) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.city
ORDER BY total_spent DESC;

-- 3. Product popularity
SELECT p.name, p.category,
       COUNT(o.id) AS times_ordered,
       SUM(o.quantity) AS units_sold
FROM products p
LEFT JOIN orders o ON p.id = o.product_id
GROUP BY p.id, p.name, p.category
ORDER BY times_ordered DESC;

-- 4. Category spending per customer
SELECT c.name, p.category,
       ROUND(SUM(o.total), 2) AS category_spend
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p ON o.product_id = p.id
GROUP BY c.id, p.category
ORDER BY c.name, category_spend DESC;

🏁 Lab 06 Complete!

You can now combine multiple tables using INNER JOIN and LEFT JOIN, use table aliases, and find rows with no matching records using the IS NULL trick.

Next up: queries inside queries.

Continue to Lab 07 — Subqueries →