Joins

Combining tables in one query: inner, left, right and full outer joins, joining more than two tables, self-joins, and the mistakes that duplicate rows.

Why joins exist

A well-designed relational database stores each fact once, in the table where it belongs (see Normalization). The shop keeps the customer's name in customers, the order date in orders, and the products of each order in order_items. The tables are linked by keys: orders.customer_id is a foreign key that points to customers.customer_id (see The Relational Model).

That design avoids contradictions, but it means a question such as "show each order with the customer's name" needs data from two tables. A join combines rows of two tables into one result row whenever a join condition is true for that pair of rows. The condition almost always compares a foreign key with the primary key it refers to.

Inner join

An inner join returns one result row for every pair of rows that satisfies the condition, and nothing for rows without a partner. The condition follows the keyword ON: 

SELECT orders.order_id, orders.order_date, customers.first_name, customers.city
FROM orders
INNER JOIN customers ON customers.customer_id = orders.customer_id
ORDER BY orders.order_id;
order_id  order_date  first_name  city
--------  ----------  ----------  ---------
101       2026-06-02  Sanne       Utrecht
102       2026-06-05  Kwame       Rotterdam
103       2026-06-18  Sanne       Utrecht
104       2026-07-01  Mei         Amsterdam
105       2026-07-09  Omar        Antwerp
106       2026-07-21  Lena        Cologne
107       2026-08-03  Kwame       Rotterdam
108       2026-08-15  Priya       NULL
109       2026-09-02  Mei         Amsterdam
110       2026-09-20  Tom         Utrecht

For each of the ten orders the database looks up the customer whose customer_id matches, and glues the two rows together. Customer 1 (Sanne) appears twice because she placed two orders; Ana, who never ordered, does not appear at all. INNER JOIN and plain JOIN mean the same thing.

Row matching between customers and orders. Sanne, customer 1, matches orders 101 and 103; Kwame, customer 2, matches orders 102 and 107; Ana, customer 8, has no matching order. An inner join returns the four matched pairs; a left join returns the same four pairs plus one row for Ana with NULL in the order columns. customers orders 1 Sanne 2 Kwame 8 Ana 101 → cust 1 103 → cust 1 102 → cust 2 107 → cust 2 NULL 4 matched pairs: inner join no match: only a left join keeps it Each line is one result row. Only three customers and four orders shown.
A join works row by row: each pair of rows that satisfies the condition becomes one result row. Venn diagrams of "overlapping circles" are a common shorthand, but they hide this: one customer row can match many order rows.

Table aliases

Writing the full table name in front of every column quickly gets long. A table alias is a short name given in the FROM clause and used everywhere else in the query. The keyword AS is optional for table aliases (Oracle does not even allow it there): 

SELECT o.order_id, o.status, c.first_name
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.order_id;
order_id  status   first_name
--------  -------  ----------
108       pending  Priya
110       pending  Tom

Prefixing columns with the alias is required when both tables have a column with that name (customer_id exists in both, so a bare customer_id would be ambiguous) and is good practice everywhere: the reader sees at once which table each column comes from.

When the key columns have the same name in both tables, standard SQL also allows JOIN customers USING (customer_id). SQLite, PostgreSQL, MySQL and Oracle support it; SQL Server does not. Avoid NATURAL JOIN, which joins on every column with the same name: adding an unrelated column such as created_at to both tables later silently changes the result.

Joining three and four tables

Each extra table takes one more JOIN … ON …. The database joins the first two tables, then joins the result to the third, and so on. An invoice for order 106 needs all four shop tables: the order, its lines, the product names, and the customer: 

SELECT o.order_id,
       c.first_name || ' ' || c.last_name  AS customer,
       p.name                              AS product,
       oi.quantity,
       oi.unit_price,
       oi.quantity * oi.unit_price         AS line_total
FROM orders AS o
JOIN customers   AS c  ON c.customer_id = o.customer_id
JOIN order_items AS oi ON oi.order_id   = o.order_id
JOIN products    AS p  ON p.product_id  = oi.product_id
WHERE o.order_id = 106
ORDER BY p.name;
order_id  customer      product             quantity  unit_price  line_total
--------  ------------  ------------------  --------  ----------  ----------
106       Lena Fischer  Ethernet cable 5 m  4         7           28
106       Lena Fischer  USB-C hub           1         45          45
106       Lena Fischer  Wireless mouse      2         25          50

Three joins for four tables: as a rule, joining n tables needs n − 1 join conditions. Each condition follows one arrow of the data model (order → customer, order → lines, line → product). The || operator concatenates text in SQLite, PostgreSQL, Oracle and SQL Server 2025 / Azure SQL; older SQL Server versions use + or CONCAT(), and MySQL uses CONCAT(). To add up the lines into one invoice total, combine the join with GROUP BY.

Outer joins: keeping unmatched rows

An inner join drops rows without a partner. Often those are exactly the rows you are interested in: customers who never ordered, products nobody bought. An outer join keeps the unmatched rows of one or both tables and fills the columns of the missing partner with NULL.

LEFT JOIN

A left join (in full, LEFT OUTER JOIN) returns everything an inner join returns, plus every row of the left table (the one before the keyword) that has no match, with NULL in the columns of the right table: 

SELECT c.customer_id, c.first_name, o.order_id, o.status
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;
customer_id  first_name  order_id  status
-----------  ----------  --------  ---------
1            Sanne       101       delivered
1            Sanne       103       delivered
2            Kwame       102       delivered
2            Kwame       107       shipped
3            Mei         104       shipped
3            Mei         109       delivered
4            Omar        105       cancelled
5            Lena        106       delivered
6            Priya       108       pending
7            Tom         110       pending
8            Ana         NULL      NULL

Ana (customer 8) now appears, with NULL for the order columns. Combined with COUNT, this gives a count per customer that includes the zeros. Count a column of the right table, not *: COUNT(o.order_id) skips the NULL and gives 0 for Ana, while COUNT(*) would count her single NULL-filled row as 1. 

SELECT c.first_name, COUNT(o.order_id) AS orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.first_name
ORDER BY orders DESC, c.first_name;
first_name  orders
----------  ------
Kwame       2
Mei         2
Sanne       2
Lena        1
Omar        1
Priya       1
Tom         1
Ana         0

Finding rows without a match

Because unmatched rows get NULL on the right side, a left join followed by WHERE right.key IS NULL keeps only the rows without a match. This pattern is called an anti-join. Products that were never ordered: 

SELECT p.product_id, p.name, p.stock
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.product_id
WHERE oi.product_id IS NULL;
product_id  name          stock
----------  ------------  -----
9           Webcam 1080p  0

The webcam has never been ordered (and is out of stock as well). Test a column that cannot be NULL in a real match, such as the key used in the join condition. The same question can also be written with NOT EXISTS, shown on Subqueries, CTEs and Views; both are correct, and modern databases often execute them the same way.

RIGHT JOIN and FULL OUTER JOIN

A RIGHT JOIN is the mirror image: it keeps every row of the right table. a RIGHT JOIN b gives the same rows as b LEFT JOIN a, so most people write left joins only and put the table they want to keep first. A FULL OUTER JOIN keeps unmatched rows from both sides.

In the shop every order belongs to a customer (the foreign key enforces it), so a full join of those tables has nothing extra to show. A full join is useful when two sources should agree but might not, such as comparing a stock count in the warehouse with the stock in the system. This example creates its own two tables: 

CREATE TABLE system_stock  (product TEXT PRIMARY KEY, units INTEGER);
CREATE TABLE counted_stock (product TEXT PRIMARY KEY, units INTEGER);
INSERT INTO system_stock  VALUES ('HDMI cable 3 m', 80), ('Laptop stand', 30), ('USB-C hub', 25);
INSERT INTO counted_stock VALUES ('HDMI cable 3 m', 78), ('USB-C hub', 25), ('Webcam 1080p', 2);

SELECT s.product AS in_system, s.units AS system_units,
       c.product AS counted,   c.units AS counted_units
FROM system_stock AS s
FULL OUTER JOIN counted_stock AS c ON c.product = s.product
ORDER BY COALESCE(s.product, c.product);
in_system       system_units  counted         counted_units
--------------  ------------  --------------  -------------
HDMI cable 3 m  80            HDMI cable 3 m  78
Laptop stand    30            NULL            NULL
USB-C hub       25            USB-C hub       25
NULL            NULL          Webcam 1080p    2

Matched products appear side by side (and the HDMI cable count differs by two); the laptop stand was not counted, and two webcams were found that the system does not know about.

Join Returns Support
INNER JOIN Matched pairs only All databases
LEFT JOIN Matched pairs + unmatched left rows All databases
RIGHT JOIN Matched pairs + unmatched right rows All major databases; SQLite since 3.39 (2022)
FULL OUTER JOIN Matched pairs + unmatched rows from both sides PostgreSQL, SQL Server, Oracle; SQLite since 3.39; not MySQL (emulate with a UNION of a left and a right join)
CROSS JOIN Every row paired with every row All databases

Cross joins and the accidental Cartesian product

A CROSS JOIN has no condition: it pairs every row of the first table with every row of the second. The result is the Cartesian product, with (rows of A) × (rows of B) rows. That is occasionally what you want — every size combined with every color to generate product variants, for example — but far more often it is a mistake: a join whose condition was forgotten.

The old comma syntax (FROM orders, customers, with the condition in WHERE) makes the mistake easy, because nothing complains when the WHERE is missing: 

SELECT COUNT(*) AS rows_without_condition
FROM orders, customers;

SELECT COUNT(*) AS rows_with_condition
FROM orders
JOIN customers ON customers.customer_id = orders.customer_id;
rows_without_condition
----------------------
80

rows_with_condition
-------------------
10

10 orders × 8 customers = 80 rows, each order paired with every customer, including the wrong ones. With real tables of a million orders and a hundred thousand customers that is 1011 rows: the query runs for hours, fills temporary disk space, or both. With four tables and one condition missing, the result is merely too large, which is harder to notice.

Self-joins

A table can be joined to itself. This is how you follow a foreign key that points into the same table: employees.manager_id refers to another row of employees. In a self-join the same table appears twice with two different aliases, one per role: 

SELECT e.name AS employee,
       e.role,
       m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.employee_id = e.manager_id
ORDER BY e.employee_id;
employee          role               manager
----------------  -----------------  ----------------
Fatima El Amrani  Director           NULL
Joris Bakker      Head of Sales      Fatima El Amrani
Yuki Tanaka       Head of Warehouse  Fatima El Amrani
Daan Visser       Sales Agent        Joris Bakker
Chloé Martin      Sales Agent        Joris Bakker
Mateus Costa      Warehouse Worker   Yuki Tanaka

Read e as "the employee" and m as "the manager". The director, Fatima, has no manager (manager_id is NULL), so an inner join would drop her; the left join keeps her with NULL as manager. A self-join only goes one level up; for the whole chain of command (employee → manager → manager's manager …) use a recursive CTE.

Join conditions versus filters: ON or WHERE?

With an inner join it makes no difference whether an extra condition is in ON or in WHERE: the result is the same. With an outer join it makes a big difference, because the two run at different moments:

  • a condition in ON decides which rows count as a match; left rows without a match are still kept, with NULLs;
  • a condition in WHERE runs after the join and removes result rows — including the NULL-filled rows the left join just added.

The task: list every customer, with their delivered orders if they have any. First with the condition in ON: 

SELECT c.first_name, o.order_id, o.status
FROM customers AS c
LEFT JOIN orders AS o
       ON o.customer_id = c.customer_id
      AND o.status = 'delivered'
ORDER BY c.customer_id, o.order_id;
first_name  order_id  status
----------  --------  ---------
Sanne       101       delivered
Sanne       103       delivered
Kwame       102       delivered
Mei         109       delivered
Omar        NULL      NULL
Lena        106       delivered
Priya       NULL      NULL
Tom         NULL      NULL
Ana         NULL      NULL

All eight customers are there; those without a delivered order get NULL. Now the same condition in WHERE: 

SELECT c.first_name, o.order_id, o.status
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status = 'delivered'
ORDER BY c.customer_id, o.order_id;
first_name  order_id  status
----------  --------  ---------
Sanne       101       delivered
Sanne       103       delivered
Kwame       102       delivered
Mei         109       delivered
Lena        106       delivered

Omar, Priya, Tom and Ana are gone. For them o.status is either another status or NULL, and NULL = 'delivered' is not true (see NULL and three-valued logic), so WHERE removes their rows. The query still says LEFT JOIN, but it behaves like an inner join.

Duplicate rows and how to spot them

A join returns one row per matching pair. When one row of table A matches several rows of table B, the A row is repeated. That is correct behavior, but it surprises people who expect "one row per customer". Which customers bought a product from the Peripherals category? 

SELECT c.customer_id, c.first_name
FROM customers AS c
JOIN orders      AS o  ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id   = o.order_id
JOIN products    AS p  ON p.product_id  = oi.product_id
WHERE p.category = 'Peripherals'
ORDER BY c.customer_id;
customer_id  first_name
-----------  ----------
1            Sanne
1            Sanne
3            Mei
5            Lena

Sanne appears twice: she bought a wireless mouse in order 101 and a keyboard in order 103, so there are two matching paths. To spot duplicates, group by what should be unique and look for counts above one: 

SELECT c.customer_id, COUNT(*) AS times_returned
FROM customers AS c
JOIN orders      AS o  ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id   = o.order_id
JOIN products    AS p  ON p.product_id  = oi.product_id
WHERE p.category = 'Peripherals'
GROUP BY c.customer_id
HAVING COUNT(*) > 1;
customer_id  times_returned
-----------  --------------
1            2

There are three ways to get one row per customer, depending on the question:

  • SELECT DISTINCT c.customer_id, c.first_name … removes identical result rows. Quick, but it hides the duplication instead of explaining it, and it does not help with sums.
  • A semi-join with EXISTS or IN ("customers for whom such an order exists") never duplicates the outer rows; see Subqueries.
  • Aggregate first, then join, when you need totals (see counting after a join).