8 Querying multiple tables

This chapter explains what makes relational databases “relational” and why splitting data across multiple, related tables is powerful. It contrasts a single, spreadsheet-like table full of repeated values with a well-designed schema that organizes data into logical groups. By introducing primary keys and foreign keys, it shows how replacing repeated strings with compact identifiers reduces redundancy, improves consistency, and makes updates reliable. Using an order tracking example, it motivates separating customers, titles, orders, and order items, and describes common relationship types, especially one-to-many, such as customer-to-orders and order-to-items.

With the relational design in place, the chapter demonstrates how to retrieve related data using joins in the FROM clause. It introduces JOIN and ON to connect tables via matching key columns, explains these predicates alongside WHERE filters, and emphasizes two-part names (table.column) to disambiguate shared column names. It scales joins from two tables to several (orderheader, customer, orderitem, title) and notes that join order in the query is often about readability as long as relationships are correctly expressed. To improve clarity, it advocates concise table aliases (for example, oh, oi, c, t) and offers simple rules for picking sensible, short alias names.

Finally, the chapter presents the older, comma-and-WHERE join style as something you may encounter but should generally avoid. While syntactically valid, pushing all join conditions into WHERE harms readability, complicates troubleshooting, and restricts you to a single join type, whereas explicit JOINs are clearer and more flexible. The chapter closes by encouraging practice with multi-table queries, mindful use of predicates, and consistent aliasing, since joins will be central to most real-world SQL.

The first ten rows in the orderitem table, which includes the column “TitleID”

shows our hypothetical table used to track orders that contains order date, title name, price, and customer name.

The results for TitleID and TitleName for the titles “Pride and Predicates” and “The Join Luck Club.

shows what it would look like if we replaced TitleName with TitleID in our hypothetical order table.

The results from the CustomerID, FirstName, and LastName for customers with the name “Chris Dixon” or “David Power”

shows our hypothetical order table with a CustomerID column to reference names in the customer table.

The results of joining the orderheader and customer tables to show CustomerID and customer name values for the first order

Customer information from the customer table, and the price of the item ordered in the first order from the orderitem table

Customer information from the customer table, the price of the item ordered in the first order from the orderitem table, and the title name from the title table

1. The results will be the same. Since our query is matching all OrderID values from the orderheader to the orderitem table, the OrderID column from either table can be used in the filtering condition to return the same results.
2. Chris Dixon placed 3 orders. You could use a query like this to get the results:

3. Eight customers placed an order in 2015:
Chris Dixon
David Power
Arnold Hinchcliffe
Keanu O’Ward
Lisa Rosenqvist
Maggie Ilott
Cora Daly
Dan Wilson
You could find them with a query like this:

4. You could write a query like this using JOINs and aliases:

5. The query executes successfully with the same result set, but as noted in this chapter, we generally try to avoid combining filtering predicates with join predicates for the sake of readability.

FAQ