Joining Multiple Tables and Avoiding Pitfalls

The Three-Table Connection

Connecting the Dots

In the real world, data is rarely in one place. To answer a question like 'Which customers bought which products?', you need to connect three lists:

• Customers: Who they are.
• Orders: What happened and when.
• Products: What was actually sold.

Think of it like a Potluck: You have a list of neighbors, a log of dishes they signed up for, and a book of recipes for those dishes. To know what Sarah is cooking, you have to hop across all three lists.

Welcome! Today we are learning how to connect multiple tables to answer complex business questions. Imagine you are organizing a community potluck. You have a list of neighbors, a log of who promised a dish, and the recipes for those dishes. To find out what Sarah is bringing, you have to look at all three. SQL does this exact 'list-hopping' using multiple JOIN statements.

• Real-world data is spread across multiple tables.
• Joining three tables requires 'list-hopping' using common columns.
• The Orders table often acts as the 'bridge' between Customers and Products.

The E-commerce Schema

To join tables successfully, you must identify the logical bridge—the columns that match between tables.

• Customers & Orders: Linked by customer_id.
• Orders & Products: Linked by product_id.

Notice that Orders is the middle ground that connects the person to the item.

Let's look at our E-commerce database. We have three tables. To connect Customers to Orders, we use the 'customer_id'. Then, to see what was actually bought, we connect Orders to Products using the 'product_id'. This creates a complete chain from the person to the product name.

• Identify matching columns (Foreign Keys).
• The Orders table contains IDs for both Customers and Products.

Syntax: Chaining JOINs

To join three tables, you simply chain the JOIN statements. SQL processes them from top to bottom.

SELECT 
  Customers.name, 
  Products.product_name
FROM Customers
JOIN Orders 
  ON Customers.customer_id = Orders.customer_id
JOIN Products 
  ON Orders.product_id = Products.product_id;

Here is the syntax. We start with our FROM table. First, we JOIN Orders ON the customer ID. Now that we have the order information, we add a second JOIN to bring in the Products table using the product ID. This chain allows us to pull the customer name and the product name into a single result set.

• Use multiple JOIN and ON clauses in sequence.
• Each JOIN must have its own ON condition.
• The order of tables in the chain must follow the logical relationships.

The Pitfall: Accidental Cross Joins

A common mistake is forgetting the ON clause. This creates a Cross Join (Cartesian Product).

The Bad Query:

SELECT Customers.name, Products.product_name
FROM Customers
JOIN Products; -- Missing ON!

The Result: Every single customer is matched with every single product in the database, creating 'junk' data.

What happens if you forget the ON clause? This is an Accidental Cross Join. Instead of matching specific orders, SQL matches every row in Table A with every row in Table B. If you have 10 customers and 100 products, you suddenly get 1,000 rows of junk data! Always verify your ON conditions if your results look duplicated.

• Forgetting an ON clause causes a Cross Join.
• Cross Joins multiply row counts (e.g., 10 customers x 100 products = 1,000 rows).
• If your results 'explode' in size, check your join conditions.

Exercise 1: The Basic Chain

Your Turn

Write a query to retrieve the name of the customer and the product_name for every order.

Join Customers to Orders, then Orders to Products.

Let's practice. Write a query that connects all three tables to show which customer bought which product. Remember to use UPPERCASE for your SQL keywords!

• Join three tables in sequence.
• Select specific columns from different tables.

Exercise 2: Adding a Filter

Refine the Results

Modify your query to only show orders where the price in the Products table is greater than 50.

Hint: Add a WHERE clause at the very end of your join chain.

Great job. Now, let's add a filter. We only want to see high-value orders where the product price is over 50. Add a WHERE clause to your previous query.

• Filters can be applied to any table in the join chain.
• The WHERE clause comes after all JOIN statements.

Exercise 3: The Debugger

Spot the Error

A colleague wrote this query, but it is returning 5,000 rows when there are only 50 orders!

SELECT * 
FROM Customers 
JOIN Orders 
  ON Customers.customer_id = Orders.customer_id 
JOIN Products;

Fix the query so it returns only the correct 50 rows.

Oh no! A colleague's query is 'exploding' the data. Look closely at the JOINs. Can you find the missing logic and fix it?

• Identify missing join conditions.
• Fix accidental cross joins.