Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 125 ยท Joins

Why Joins?

Understand why relational data is split across tables and how SQL joins put it back together. Learn the join mindset, keys, and the join types you'll use.

Look at an order in the shop database and you'll see customer_id = 21. Not a name, not a city, just a number. To find out who placed the order, you have to look up customer 21 in another table. A join does that lookup for you, for every row at once, inside a single query.

Joins are what make relational databases relational. Nearly every useful query in a real application touches more than one table: orders with their customers, products with their categories, employees with their departments. This lesson explains why data is split up in the first place, how to think about a join, and which kinds of joins exist. The following lessons cover each join type in depth.

Why data lives in separate tables

Imagine storing everything about an order in one wide table: the order date, plus the customer's name, email, city and country, repeated on every order the customer ever placed. That design has real problems:

  • Duplication. A customer with 6 orders has their email stored 6 times.
  • Update anomalies. If Rania changes her email, you must update every one of her orders, and missing one leaves the data contradicting itself.
  • Missing things. A customer who hasn't ordered yet has nowhere to live, because a row needs an order.

Relational design solves this by storing each kind of thing once, in its own table, and connecting tables with keys. The shop database looks like this:

Table One row perโ€ฆ Primary key Points to
categories category id
products product id category_id โ†’ categories.id
customers customer id
orders order id customer_id โ†’ customers.id
order_items line in an order id order_id โ†’ orders.id, product_id โ†’ products.id

A primary key identifies one row in its own table. A foreign key is a column that holds another table's primary key value, like orders.customer_id. You met both briefly in Databases, Tables and Keys; the design side is covered in Foreign Keys and Normalization.

Splitting data like this is called normalizing it. Joins are how you put it back together when you read it.

Looking things up by hand

Here are a few orders. They tell you when and what status, but the customer is only a number:

SQL Orders know only a customer id ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

To find the names, you could run a second query with those ids:

SQL The manual lookup ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

That works for three orders. It doesn't work for 120 orders, and it falls apart as soon as you want to filter or group by a customer's country. Doing lookups in application code, one query per row, is also a famous performance problem (the "N+1 queries" problem, covered in Writing Fast Queries). The database can do all the lookups in one go.

Your first join

A join lists both tables and says how their rows match:

SQL Orders with customer names ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Read the FROM part as: take the orders, and for each one attach the customer whose id equals the order's customer_id. The ON clause is the join condition. The result is a new, wider table: every column of orders next to every column of customers, and you pick the ones you want in SELECT.

Because both tables have an id column, you write orders.id or customers.id to say which one you mean. That's called qualifying a column. Repeating whole table names gets long, so people almost always use short table aliases:

SQL
SELECT o.id AS order_id, o.ordered_at, c.first_name, c.last_name
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id;

If you forget to qualify a column that exists in both tables, the database refuses to guess. This example fails on purpose:

SQL An ambiguous column (error) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQLite reports Error: ambiguous column name: id. first_name and status are fine, because each exists in only one of the two tables. A good habit is to qualify every column in a join query, even unambiguous ones: readers then know at a glance which table each value comes from.

The join mindset

A helpful way to picture a join is a nested loop:

  1. Take a row from the left table (orders).
  2. Look at every row of the right table (customers) and test the ON condition.
  3. For every pair where the condition is true, produce one output row combining both.
  4. Repeat for the next left row.

Real databases don't literally loop like this; they use indexes and smarter algorithms (you'll see them in Reading Query Plans). But the result is always the same as this picture, and the picture predicts how many rows you'll get.

Row counts follow the relationship

The keys tell you what to expect:

  • Many-to-one (orders โ†’ customers): each order has exactly one customer, so joining orders to customers keeps one row per order. 120 orders in, 120 rows out.
  • One-to-many (customers โ†’ orders): a customer with 6 orders appears on 6 rows. A customer with no orders disappears (with the default kind of join).
SQL Counting rows before and after a join ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

All 120 orders find their customer. But only 34 of the 40 customers show up after the join, because 6 customers have never ordered. Whether that's what you want depends on the question, and it's exactly the difference between the join types below.

Count before you trust. When a join result looks wrong, compare COUNT(*) before and after the join. Fewer rows than expected means rows found no match; more rows than expected means a row matched several partners, often because the ON condition is incomplete.

Join on the right keys

The join condition must connect a foreign key to the primary key it refers to. Joining on the wrong columns doesn't raise an error; it quietly produces nonsense. This query joins orders.id to customers.id, as if order 5 belonged to customer 5:

SQL A wrong join condition ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The query runs, but customer_id and joined_customer disagree on every row: order 9 belongs to customer 21, yet it was matched with customer 9. Ids are just numbers, so the database can't tell that the comparison makes no sense. The schema can: follow the foreign key. In orders, the column that points to customers is customer_id, so the condition is c.id = o.customer_id.

Chaining joins

A query can join as many tables as it needs. Each JOIN โ€ฆ ON adds one more table to the growing result. To list what was in an order, you need order_items (quantities) and products (names):

SQL What was in order 4? ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Four tables, three join conditions, each following one foreign key. With n tables you normally need n โˆ’ 1 join conditions. Joining Many Tables covers longer chains and their pitfalls.

The kinds of joins

SQL has several join types. They differ in what happens to rows that don't find a match:

Join Keeps Typical question Lesson
INNER JOIN (or just JOIN) Only matching pairs Orders with their customers INNER JOIN
LEFT JOIN All left rows, matched or not All customers, with orders if any LEFT JOIN
RIGHT JOIN All right rows Same as LEFT, tables swapped RIGHT and FULL OUTER JOIN
FULL OUTER JOIN All rows from both sides Compare two lists, show both gaps RIGHT and FULL OUTER JOIN
CROSS JOIN Every combination All sizes ร— all colors CROSS JOIN
Self join A table joined to itself Employees and their managers Self Joins

JOIN on its own means INNER JOIN. All of these are standard SQL, and SQLite 3.39+ supports every one of them (older SQLite versions lacked RIGHT and FULL).

The old comma syntax

You'll also meet joins written with commas in FROM and the condition in WHERE:

SQL
-- Old style (still valid, but avoid)
SELECT o.id, c.first_name
FROM orders AS o, customers AS c
WHERE c.id = o.customer_id;

It returns the same rows as an inner join. The problem is that join conditions get mixed up with ordinary filters, and if you forget one, you silently get every combination of rows (a cross join). The explicit JOIN โ€ฆ ON syntax, standard since SQL-92, keeps each condition next to its table. Use it in new code.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does the orders table store customer_id instead of the customer's name and email?

    Storing the customer once and referring to it by key avoids repeated data and update anomalies. Joins rebuild the combined view when you need it.
  2. 2. Which ON condition correctly joins orders to customers?

    The foreign key orders.customer_id holds the primary key value customers.id, so those two columns must match.
  3. 3. What does SELECT id FROM orders JOIN customers ON customers.id = orders.customer_id; do in SQLite?

    Both tables have an id column, so you must qualify it, for example orders.id or o.id.
  4. 4. You join customers (40 rows) to orders with a plain JOIN. Six customers have no orders and one has six. Which statement is true?

    An inner join produces one row per matching pair. No match means no row; several matches mean several rows.

Summary

  • Relational databases store each kind of thing once and link tables with primary keys and foreign keys.
  • A join combines rows from two tables where the ON condition holds, producing a wider result.
  • Qualify columns with table names or aliases (o.id, c.id); unqualified shared names are ambiguous.
  • Join a foreign key to the primary key it references. Wrong conditions run without errors but give nonsense.
  • Row counts follow the relationship: many-to-one keeps the row count, one-to-many multiplies rows, and unmatched rows vanish from inner joins.
  • Join types differ in how they treat unmatched rows: INNER, LEFT, RIGHT, FULL, plus CROSS and self joins. Prefer JOIN โ€ฆ ON over the old comma syntax.

Next: INNER JOIN, where you'll master the most common join: ON, USING, multi-column conditions and why to avoid NATURAL JOIN.