Course contentsShow
← Computer Science
Lesson 1586 of 2,87236. Database FundamentalsFree lesson

Three-Table Joins

Extending join operations to connect three tables sequentially in a single query.

Three-Table Joins

What You'll Learn

You'll learn how to extend join operations beyond two tables to connect three (or more) tables in a single SQL query.

Connecting Multiple Tables

So far, you've joined two tables together. But real-world databases often require pulling data from three or more related tables simultaneously. A three-table join simply chains multiple join operations together in one query.

Think of it like connecting three pieces of a puzzle: Table A connects to Table B through a foreign key, and Table B connects to Table C through another foreign key. You can traverse these relationships in a single query.

How It Works

The syntax follows a pattern: start with one table, join the second, then join the third. Each join uses its own ON condition to specify how tables relate.

SELECT columns
FROM table1
INNER JOIN table2 ON table1.key = table2.foreign_key
INNER JOIN table3 ON table2.key = table3.foreign_key
WHERE conditions;

Example scenario: Suppose you have customers, orders, and products tables. To see which customers bought which products:

SELECT customers.name, products.product_name, orders.order_date
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
INNER JOIN products ON orders.product_id = products.product_id;

Notice how orders acts as a bridge connecting customers to products.

Key Considerations

  • Order matters for readability, but the query optimizer may rearrange internally
  • You can mix join types (inner, left, right) as needed
  • Each INNER JOIN requires its own ON clause
  • More tables mean more potential for performance impact—consider indexes

Key Takeaway: Three-table joins extend two-table joins by chaining additional join operations, allowing you to traverse multiple foreign key relationships in a single query to combine data from three or more related tables.