Querying Multiple Tables

9 min read•Learning SQL

Querying Multiple Tables

Real-world databases spread data across multiple related tables. To answer questions like "What books did Alice order?", you need to combine data from customers, orders, order_items, and books tables. This is where joins come in.

Why Multiple Tables?

Consider storing everything in one giant table:

order_id	customer_name	customer_email	book_title	book_price	quantity
1	Alice Chen	alice@...	1984	15.99	1
1	Alice Chen	alice@...	Animal Farm	12.99	1
2	Bob Martinez	bob@...	1984	15.99	2

Problems with this approach:

Redundancy: Alice's info repeated for each item she orders
Update anomalies: Change Alice's email? Update every row
Inconsistency risk: Typos create multiple "versions" of Alice
Wasted space: Same data stored multiple times

Normalization splits data into related tables, each storing one type of entity. Joins reconstruct the complete picture when needed.

Join Fundamentals

A join combines rows from two (or more) tables based on a related column. The basic syntax:

SQL

SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column;

Joining Books to Authors

SQL

SELECT
    books.title,
    books.price,
    authors.first_name,
    authors.last_name
FROM books
JOIN authors ON books.author_id = authors.author_id;

title	price	first_name	last_name
1984	15.99	George	Orwell
Animal Farm	12.99	George	Orwell
Pride and Prejudice	14.99	Jane	Austen
One Hundred Years of Solitude	18.99	Gabriel	García Márquez
Norwegian Wood	16.99	Haruki	Murakami
Kafka on the Shore	17.99	Haruki	Murakami

The ON clause specifies how rows should be matched. For each book, the database finds the author row where author_id values match.

Table Aliases

When joining tables, aliases make queries more readable:

SQL

SELECT
    b.title,
    b.price,
    a.first_name,
    a.last_name
FROM books AS b
JOIN authors AS a ON b.author_id = a.author_id;

Note

The AS keyword is optional. FROM books b works the same as FROM books AS b. Most developers omit it for brevity.

Types of Joins

INNER JOIN (Default)

INNER JOIN returns only rows that have matching values in both tables.

SQL

SELECT b.title, a.last_name
FROM books b
INNER JOIN authors a ON b.author_id = a.author_id;

If an author has no books, they don't appear. If a book somehow had an invalid author_id, it wouldn't appear either.

LEFT (OUTER) JOIN

LEFT JOIN returns all rows from the left table, plus matching rows from the right table. If there's no match, NULL fills in the right side.

SQL

-- All customers and their orders (if any)
SELECT
    c.first_name,
    c.last_name,
    o.order_id,
    o.total_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

first_name	last_name	order_id	total_amount
Alice	Chen	1	31.98
Alice	Chen	3	46.97
Bob	Martinez	2	18.99
Carol	Taylor	NULL	NULL

Carol has no orders, but she still appears with NULL values for order columns.

RIGHT (OUTER) JOIN

RIGHT JOIN is the mirror of LEFT JOIN—all rows from the right table, matches from the left:

SQL

SELECT
    c.first_name,
    o.order_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

Note

Most developers prefer LEFT JOIN and simply swap the table order when needed. It's more intuitive to read "all from this table, plus matching from that table."

FULL (OUTER) JOIN

FULL JOIN returns all rows from both tables, with NULL where there's no match:

SQL

SELECT
    c.first_name,
    o.order_id
FROM customers c
FULL JOIN orders o ON c.customer_id = o.customer_id;

This would show:

Customers with orders
Customers without orders (NULL order data)
Orders without customers (NULL customer data, if any existed)

Warning

MySQL doesn't support FULL OUTER JOIN directly. You need to combine LEFT JOIN and RIGHT JOIN with UNION.

CROSS JOIN

CROSS JOIN produces the Cartesian product—every combination of rows from both tables:

SQL

-- Every possible author-book pairing (usually not what you want!)
SELECT a.last_name, b.title
FROM authors a
CROSS JOIN books b;

With 4 authors and 6 books, this returns 24 rows. Cross joins are occasionally useful for generating combinations or test data.

Joining Multiple Tables

Real queries often join several tables. Just chain the JOINs:

SQL

-- Complete order details
SELECT
    c.first_name,
    c.last_name,
    o.order_id,
    o.order_date,
    b.title,
    oi.quantity,
    oi.unit_price
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN books b ON oi.book_id = b.book_id
ORDER BY o.order_id, b.title;

first_name	last_name	order_id	order_date	title	quantity	unit_price
Alice	Chen	1	2024-01-15	1984	1	15.99
Alice	Chen	1	2024-01-15	Animal Farm	1	12.99
Bob	Martinez	2	2024-01-16	One Hundred Years...	1	18.99
Alice	Chen	3	2024-01-17	Animal Farm	1	12.99
Alice	Chen	3	2024-01-17	Norwegian Wood	1	16.99
Alice	Chen	3	2024-01-17	Pride and Prejudice	1	14.99

Self Joins

A table can join to itself. This is useful for hierarchical data or comparing rows within the same table.

Finding Books by the Same Author

SQL

-- Find pairs of books by the same author
SELECT
    b1.title AS book1,
    b2.title AS book2,
    a.last_name AS author
FROM books b1
JOIN books b2 ON b1.author_id = b2.author_id AND b1.book_id < b2.book_id
JOIN authors a ON b1.author_id = a.author_id;

book1	book2	author
1984	Animal Farm	Orwell
Norwegian Wood	Kafka on the Shore	Murakami

The condition b1.book_id < b2.book_id prevents duplicate pairs and self-matches.

Join Conditions

Equality (Equi-Join)

Most joins use equality:

SQL

ON books.author_id = authors.author_id

Non-Equality Joins

Sometimes you need other comparisons:

SQL

-- Find books more expensive than others by the same author
SELECT
    b1.title AS expensive_book,
    b1.price AS price,
    b2.title AS cheaper_book,
    b2.price AS lower_price
FROM books b1
JOIN books b2 ON b1.author_id = b2.author_id
             AND b1.price > b2.price;

Multiple Conditions

Join conditions can have multiple parts:

SQL

ON t1.column1 = t2.column1
   AND t1.column2 = t2.column2

USING Clause

When joining on identically-named columns, USING is a shorthand:

SQL

-- Instead of:
FROM books b JOIN authors a ON b.author_id = a.author_id
 
-- You can write:
FROM books b JOIN authors a USING (author_id)

The column appears only once in the result (not prefixed with table name).

Natural Join

NATURAL JOIN automatically joins on all columns with matching names:

SQL

SELECT * FROM books NATURAL JOIN authors;

Warning

Avoid NATURAL JOIN in production code. It's fragile—adding a column with a matching name can silently change your query's behavior. Always be explicit about join conditions.

Combining Joins with Filters

Add WHERE clauses after all JOINs:

SQL

SELECT
    c.first_name,
    b.title,
    oi.quantity
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN books b ON oi.book_id = b.book_id
WHERE c.first_name = 'Alice'
  AND o.order_date >= '2024-01-01';

Join Performance Tips

Index foreign keys: Joins on indexed columns are much faster
Filter early: WHERE conditions that reduce rows help performance
Select only needed columns: Don't SELECT * across large joins
Be mindful of outer joins: They can prevent some optimizations

SQL

-- Good: Filter before joining many tables
SELECT b.title, a.last_name
FROM books b
JOIN authors a ON b.author_id = a.author_id
WHERE b.price > 15;
 
-- The database can filter books first, then join fewer rows

Common Mistakes

Forgetting the ON Clause

SQL

-- Missing ON becomes a CROSS JOIN (probably wrong!)
SELECT * FROM books JOIN authors;

Ambiguous Column Names

SQL

-- Error: which author_id?
SELECT author_id FROM books JOIN authors ON books.author_id = authors.author_id;
 
-- Fix: qualify the column
SELECT books.author_id FROM books JOIN authors ON books.author_id = authors.author_id;

Wrong Join Type

SQL

-- INNER JOIN misses customers without orders
SELECT c.name, COUNT(o.order_id)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;
 
-- LEFT JOIN includes all customers
SELECT c.name, COUNT(o.order_id)
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

Practical Examples

Example 1: Order Summary with Customer Info

SQL

SELECT
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    c.email,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name, c.email
ORDER BY total_spent DESC;

Example 2: Book Catalog with Author Details

SQL

SELECT
    b.title,
    CONCAT(a.first_name, ' ', a.last_name) AS author,
    a.nationality,
    b.publication_year,
    b.price
FROM books b
JOIN authors a ON b.author_id = a.author_id
ORDER BY a.last_name, b.publication_year;

Example 3: Finding Customers Who Bought Specific Books

SQL

SELECT DISTINCT
    c.first_name,
    c.last_name,
    c.email
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN books b ON oi.book_id = b.book_id
WHERE b.title = '1984';

What's Next

The next chapter explores set operations—UNION, INTERSECT, and EXCEPT—which combine result sets from multiple queries in different ways than joins.