Why JOINs Matter

In the real world, data is never in one table. A ShopStream order involves a customer, multiple products, and a seller. To answer questions like "What did each customer buy?" or "Which products generated the most revenue?", you need to connect these tables.

That's where JOINs come in. A JOIN combines rows from two or more tables based on a common column (usually a foreign key). Once you master JOINs, you can pull insights from your entire database at once.

By the end of this lesson, you'll know four JOIN types and how to combine 4+ tables in a single query.

Four JOIN Types: Quick Visual Guide

Here's the essence of each JOIN type:

INNER JOIN
┌──────────────────────────────────┐
│ Only matching rows from both │
│ tables (intersection) │
└──────────────────────────────────┘

LEFT JOIN
┌──────────────────────────────────┐
│ All rows from LEFT table, plus │
│ matching rows from RIGHT table │
│ (unmatched right rows = NULL) │
└──────────────────────────────────┘

RIGHT JOIN
┌──────────────────────────────────┐
│ All rows from RIGHT table, plus │
│ matching rows from LEFT table │
│ (unmatched left rows = NULL) │
└──────────────────────────────────┘

FULL OUTER JOIN
┌──────────────────────────────────┐
│ All rows from both tables │
│ (unmatched columns = NULL) │
└──────────────────────────────────┘

The key difference: how each JOIN handles unmatched rows.

INNER JOIN — Only Matching Rows

An INNER JOIN returns only rows where a match exists in both tables. If a customer has no orders, they won't appear. If an order has no matching customer (data integrity issue), that row is excluded.

Use INNER JOIN when: You only care about rows with data in both tables.

Q1: Orders with Customer Names

Pull all orders with their customer details:

sql
10 rows returned. Each order linked to exactly one customer.

Breakdown:

  • FROM orders o — Left table (orders).
  • INNER JOIN customers c ON o.customer_id = c.customer_id — Join condition. Match when customer IDs are equal.
  • ORDER BY o.order_date DESC — Sort by newest orders first.
  • Result: Only orders with a matching customer appear. If a customer never ordered, they're not in this result.

LEFT JOIN — All Left Table Rows

A LEFT JOIN returns all rows from the left table, plus matching rows from the right table. If no match exists, the right table columns show NULL.

Use LEFT JOIN when: You want to include all records from the primary table, even if they don't match the secondary table.

Q2: All Customers and Their Order Count (including those with zero orders)

Find every customer and how many orders they've placed (including customers who never ordered):

sql
100 rows returned. Includes customers with 0 orders.

Breakdown:

  • FROM customers c — Left table. All customers are included.
  • LEFT JOIN orders o — Right table. Matching rows added, unmatched rows show NULL in order columns.
  • COUNT(o.order_id) — Counts non-NULL order IDs. If no orders, COUNT returns 0.
  • Result: Even customers with zero orders appear (order_count = 0).

RIGHT JOIN and FULL OUTER JOIN

RIGHT JOIN is the mirror of LEFT JOIN—all right table rows, plus matches from the left. FULL OUTER JOIN includes all rows from both tables, with NULLs for unmatched columns.

Q3: All Products and How Many Times They Were Ordered (RIGHT JOIN)

Every product, even those never ordered:

sql
10 rows returned. Products sorted by least-ordered first.

Breakdown:

  • RIGHT JOIN products p — All products included, regardless of whether they've been ordered.
  • COUNT(oi.item_id) — Returns 0 for products never in an order.

Q4: FULL OUTER JOIN — Customers and Sellers

Find mismatches where no customer exists in a seller's city, or vice versa:

sql
0 rows returned. All sellers match with at least one customer city.

Breakdown:

  • FULL OUTER JOIN — Returns all rows from both tables, NULLs where no match.
  • WHERE c.name IS NULL OR s.company_name IS NULL — Filter for unmatched rows only.

Multi-Table JOINs — Connecting 4+ Tables

In real scenarios, you often need data from 3, 4, or more tables. Chain JOINs together: each JOIN references the previous table(s).

Q5: Complete Order View (4-Table JOIN)

For each order item, show the order ID, customer name, product name, quantity, and total:

sql
20 rows returned. Sample shows Alexander Brown's order of Building Blocks.

Breakdown:

  • FROM orders o — Start with orders.
  • JOIN customers c — Add customer info.
  • JOIN order_items oi — Add line items for each order.
  • JOIN products p — Add product details.
  • Result: One row per order item with full context (who ordered what).

Order matters: Join tables in a logical sequence. Start with the main table (orders), then join related tables (customers, order_items, products). Each JOIN builds on previous ones.

Self-Join — A Table Joined to Itself

Sometimes you need to compare rows within the same table. For example, find all customers in the same city. Use a self-join by aliasing the same table twice.

Q6: Customers in the Same City

Find pairs of customers who live in the same city:

sql
10 rows returned. Jake Martin pairs with other Seattle customers.

Breakdown:

  • FROM customers a JOIN customers b — Same table with two aliases (a, b).
  • ON a.city = b.city — Match rows with the same city.
  • AND a.customer_id < b.customer_id — Avoid duplicates. Only show each pair once (a < b prevents the reverse pair).
  • Result: Unique customer pairs sharing a city.

JOINs with Aggregation and Filtering

Combine JOINs with GROUP BY and WHERE to summarize multi-table data and filter results.

Q7: Revenue by Seller (JOINs + GROUP BY + WHERE)

For each seller, calculate total revenue from delivered orders only:

sql
27 rows returned. FashionForward Co leads with $11,104.47 revenue.

Breakdown:

  • FROM sellers s — Start with sellers.
  • JOIN order_items oi, JOIN orders o — Link to items and orders.
  • WHERE o.status = 'delivered' — Filter to delivered orders only.
  • GROUP BY s.seller_id, ... — Aggregate per seller.
  • SUM(oi.quantity * oi.unit_price) — Calculate revenue per seller.
  • Result: Seller rankings by revenue.

Q8: High-Rated Products Ordered (JOINs + WHERE)

Find products with rating > 4.0 that have been ordered:

sql
20 rows returned. Water Bottle leads with 5.00 rating.

Breakdown:

  • JOIN order_items oi — Only include products in at least one order (auto-filtered by JOIN).
  • WHERE p.avg_rating > 4.0 — Filter for highly-rated products.
  • DISTINCT — Remove duplicate product names (since a product might appear in multiple orders).
  • Result: Best-rated products customers actually bought.

Challenge

Write a query to find the top 5 customers by total spending (sum of order amounts):

  • Use an INNER JOIN between customers and orders.
  • Group by customer.
  • Sum order amounts.
  • Sort by total spending DESC.
  • Return customer name and total_spent.
Show hint

Follow this structure:

sql

Test Your Knowledge

Question 1 of 5
Which JOIN returns only matching rows from both tables?
Question 2 of 5
What happens to unmatched left-table rows in a LEFT JOIN?
Question 3 of 5
How many tables can you JOIN in one query?
Question 4 of 5
What is a self-join?
Question 5 of 5
Do you need GROUP BY when using aggregates (SUM, COUNT) with JOINs?
0
/5
Africa & India's Best AI Academy
🤖 AI-first 🎓 Placement Assistance

Tables connected. Your queries just got powerful.

Join the Best AI Program: The AI-Era Data Engineer — 5 months, fully online, with placement assistance. Week 1 is free.

Explore The AI-Era Data Engineer →