SQL JOIN

Combine rows from related tables with INNER JOIN and LEFT JOIN, using keys and table aliases.

Lesson 12 of 102Beginner18 min read

Good databases avoid repeating information. The orders table doesn't store each customer's name and city; it stores a customer_id that points to the right row in customers. That saves space and means a customer's details only need updating in one place.

The cost is that the information you need is spread across tables. To show "Ana bought a backpack", you need the order, the customer and the product together. A JOIN combines rows from two or more tables by matching the values in their key columns.

In this lesson you'll learn how foreign keys connect tables, write INNER JOIN and LEFT JOIN queries, join three tables at once and find rows that have no match.

INNER JOIN

SQL
SELECT orders.id, customers.name, orders.quantity
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;
  • JOIN names the second table.
  • ON says how rows match: here, an order's customer_id equals a customer's id.
  • Write table.column when the same column name exists in both tables, as id does here.
  • An inner join only returns rows that have a match in both tables. Plain JOIN means INNER JOIN.

Table aliases and three-table joins

Typing full table names gets long. Give each table a short alias right after its name and use that instead:

SQL
SELECT c.name, p.name AS product, o.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products  p ON o.product_id  = p.id;

You can keep adding JOIN ... ON ... lines to bring in more tables. Each join links one new table to a table that's already in the query.

LEFT JOIN

A LEFT JOIN returns every row from the first (left) table, whether or not it has a match. Where there's no match, the columns from the right table are filled with NULL.

SQL
SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Ema has never ordered, so an inner join would leave her out completely. The left join includes her with NULL as her order_id. Add WHERE o.id IS NULL to find only the rows with no match, such as customers who have never bought anything.

SQL JOIN Syntax

JOIN

SELECT a.column, b.column
FROM table_a a
INNER JOIN table_b b ON a.foreign_key = b.id;

SELECT a.column, b.column
FROM table_a a
LEFT JOIN table_b b ON b.foreign_key = a.id;

SQL JOIN Summary

  • A foreign key column stores the primary key of a row in another table.
  • JOIN table ON a.key = b.key combines rows whose keys match.
  • INNER JOIN keeps only rows with a match in both tables.
  • LEFT JOIN keeps every row from the left table and fills missing matches with NULL.
  • Short aliases like o, c and p keep multi-table queries readable.

SQL JOIN Examples

Each example shows the code, the output it produces and what each part does.

Example 1

Orders with customer names

SQL
SELECT o.id, c.name, o.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.id
ORDER BY o.id;
Output
id | name  | quantity
---+-------+---------
1  | Ana   | 1
2  | Ben   | 5
3  | Ana   | 10
4  | Dev   | 1
5  | Chloe | 2
6  | Ben   | 1
Line-by-line explanation
  1. Lines 2 to 3Each order is matched to the customer whose id equals its customer_id.
  2. Line 1Ana appears twice because she placed two orders.
Example 2

Three tables and a calculated total

SQL
SELECT c.name     AS customer,
       p.name     AS product,
       o.quantity,
       o.quantity * p.price AS line_total
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products  p ON o.product_id  = p.id
ORDER BY o.id;
Output
customer | product      | quantity | line_total
---------+--------------+----------+-----------
Ana      | Backpack     | 1        | 34.99
Ben      | Notebook     | 5        | 17.50
Ana      | Pen          | 10       | 12.00
Dev      | Desk lamp    | 1        | 24.50
Chloe    | Water bottle | 2        | 24.00
Ben      | Water bottle | 1        | 12.00
Line-by-line explanation
  1. Lines 1 to 2Both tables have a name column, so aliases give them clear headings.
  2. Line 4Columns from different tables can be used in one calculation.
  3. Lines 6 to 7Two joins bring in the customer and the product for every order.
Example 3

Orders per customer, including nobody

SQL
SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY orders DESC, c.name;
Output
name  | orders
------+-------
Ana   | 2
Ben   | 2
Chloe | 1
Dev   | 1
Ema   | 0
Line-by-line explanation
  1. Line 3LEFT JOIN keeps Ema even though she has no orders.
  2. Line 1COUNT(o.id) counts only real orders, so Ema gets 0 instead of 1.
Example 4

Customers who have never ordered

SQL
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
Output
name
----
Ema
Line-by-line explanation
  1. Line 4After a LEFT JOIN, a NULL on the right side means "no matching order".

Common SQL JOIN Mistakes

Forgetting the ON condition

Don't
SELECT c.name, o.id
FROM orders o
JOIN customers c;
Do
SELECT c.name, o.id
FROM orders o
JOIN customers c ON o.customer_id = c.id;

Without ON, many databases pair every order with every customer: 6 x 5 = 30 meaningless rows.

Ambiguous column names

Don't
SELECT id, name
FROM orders o
JOIN customers c ON o.customer_id = c.id;
Do
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

Both tables have an id column, so the database can't tell which one you mean.

Using INNER JOIN when you need every row

Don't
SELECT c.name, COUNT(o.id) AS orders
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;
Do
SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

The inner join silently drops Ema, so the report looks complete but isn't.

SQL JOIN in Real Projects

Joins everywhere

An order confirmation email joins orders, customers and products. A social network joins posts with the users who wrote them, and a school system joins students, classes and grades. Whenever you see information from several "things" on one screen, a join is almost certainly behind it.

SQL JOIN FAQ

Is JOIN the same as INNER JOIN?

Yes. JOIN on its own means INNER JOIN in every major database. Writing INNER JOIN is a little more explicit and makes the difference from LEFT JOIN obvious when you read a query later.

Why does my join return more rows than I expected?

Because the relationship is one-to-many: a customer with three orders appears three times when you join customers to orders. That's correct for an order list, but it inflates sums and counts of customer data. Aggregate the many side first, or count with COUNT(DISTINCT customers.id).

Does the order of tables in a join matter?

For an INNER JOIN, the result is the same either way, and the database chooses its own order for speed. For a LEFT JOIN, order matters: the table written first keeps all its rows, so customers LEFT JOIN orders and orders LEFT JOIN customers answer different questions.

What is the difference between ON and USING?

ON gives the full join condition, such as ON orders.customer_id = customers.id, and works for any column names. USING (customer_id) is a shortcut when both tables have a column with exactly the same name, and it shows that column only once in the result. SQL Server doesn't support USING.

Can I join on columns that aren't keys?

Yes. Any condition can be used, such as matching on city or on a date range. Joins on keys are the most common because keys are unique and indexed. Joining on non-unique or unindexed columns can multiply rows unexpectedly and run slowly on large tables.

SQL JOIN Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the query that lists each order with its product name.

SELECT o.id, p.name
FROM orders o
 products p  o.product_id = p.id;
Multiple choicemedium

Which join returns every customer, even those without orders?

Predict the outputmedium

How many rows does this return?

SELECT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Leeds';
Predict the outputhard

After a LEFT JOIN from customers to orders, what is o.id for Ema?

Predict the outputhard

Using this course's tables, how many rows does an INNER JOIN of customers to orders return?

SQL JOIN Quiz

5 questions to check what you learned.

  1. What does a foreign key store?

    • A password
    • The primary key of a row in another table
    • A copy of the whole related row
    • The table's name

  2. What does INNER JOIN return?

    • All rows from both tables
    • All rows from the left table
    • Rows with no match
    • Only rows that have a match in both tables

  3. Why use aliases like o and c?

    • They make queries shorter and clear up which table a column comes from
    • They make queries run faster
    • They rename the tables permanently
    • They are required for every join

  4. How do you find customers who have never placed an order?

    • LEFT JOIN orders and keep rows WHERE o.id IS NULL
    • INNER JOIN orders WHERE o.id = 0
    • SELECT customers WHERE orders = NULL
    • RIGHT JOIN customers WHERE c.id IS NULL

  5. What does a table alias like customers c do?

    • Copies the table
    • Renames the table permanently
    • Creates an index
    • Gives the table a short name to use in the rest of the query

Related SQL topics

05ORDER BY & GROUP BYSort results, return only the top rows, and summarise data with COUNT, SUM, AVG and GROUP BY.01IntroductionWhat databases, tables, rows and columns are, and how to write your first SQL query.08INSERT, UPDATE, DELETEAdd new rows, change existing data and remove rows safely, without losing anything by accident.
Esc
↑↓ to navigateEnter to open