Lesson 8 of 15 · 20 min
Joining three or more tables
By the end of this lesson you can plan a join path through a schema, chain several joins so each new table connects to one already in the query, predict the grain of the result, and keep outer joins outer all the way down the chain.
A query can join as many tables as it needs. Each JOIN adds one table and its ON clause connects it to a table already in the query. PostgreSQL processes the joins as one combined operation, but it helps to read them top to bottom: the first join produces an intermediate result, the next join attaches a new table to that result, and so on.
Plan the path first
Before writing anything, find the path between the tables that hold your columns. In shop the main path is:
customers → orders → order_items → products → categories (→ parent category)
Every arrow is a foreign key. To get customer last name and product name on one row, you must walk through orders and order_items, even if you don't select any of their columns: there is no direct link from customers to products.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons