Introduction to Joins

A well-normalized database rarely keeps everything in one table. Customer details live in a customers table, their orders live in an orders table, the items on each order live in yet another table, and so on. Splitting data this way avoids repeating the same customer name or address on every single order row — which is exactly what normalization is designed to do. But once data is spread across tables, you need a way to bring it back together for reporting and everyday queries. That is what a join does.

A join combines rows from two (or more) tables based on a related column between them — typically a primary key in one table and a matching foreign key in the other. Instead of manually looking up a customer, then separately looking up their orders, and stitching the results together in application code, a join lets the database engine do that matching in a single query.

The general syntax pattern

Every join follows roughly the same shape: pick the columns you want, name the two tables, and describe how rows in one relate to rows in the other.

General join syntax

SQL

SELECT columns
FROM tableA
JOIN tableB
  ON tableA.matching_column = tableB.matching_column;

The ON clause is the heart of the join — it tells the database exactly which rows in tableA correspond to which rows in tableB. Get the ON clause wrong (or forget it entirely) and you either get a query error, a result set full of nonsense matches, or — as you will see in the CROSS JOIN page — every possible combination of rows from both tables.

The example schema used throughout this section

To keep every join example concrete and easy to compare, the rest of the pages in this section reuse the same two small tables: customers and orders. A customer can place zero, one, or many orders, and every order belongs to exactly one customer — a classic one-to-many relationship linked by customer_id.

Reference schema: customers and orders

SQL

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(50),
  city        VARCHAR(50)
);

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT,
  amount      DECIMAL(10, 2),
  order_date  DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO customers (customer_id, name, city) VALUES
  (1, 'Alice', 'New York'),
  (2, 'Bob',   'Boston'),
  (3, 'Carol', 'Chicago'),
  (4, 'Dave',  'Denver');

INSERT INTO orders (order_id, customer_id, amount, order_date) VALUES
  (101, 1, 250.00, '2024-01-05'),
  (102, 1,  75.50, '2024-02-10'),
  (103, 2, 300.00, '2024-01-20'),
  (104, 3, 120.00, '2024-02-15'),
  (105, 99, 45.00, '2024-03-01');

Notice two intentional wrinkles that show up in the join pages ahead: Dave (customer_id 4) has placed no orders at all, and order 105 references customer_id 99, which does not exist in the customers table. Real-world data is messy in exactly these ways, and different join types handle that messiness differently.

customers	orders
1 — Alice — New York	101 — cust 1 — 250.00
2 — Bob — Boston	102 — cust 1 — 75.50
3 — Carol — Chicago	103 — cust 2 — 300.00
4 — Dave — Denver (no orders)	104 — cust 3 — 120.00
	105 — cust 99 — 45.00 (orphan order)

The join types covered in this section

Join type	What it returns
INNER JOIN	Only rows that have a match in both tables.
LEFT (OUTER) JOIN	All rows from the left table, plus matches from the right (NULL when there is none).
RIGHT (OUTER) JOIN	All rows from the right table, plus matches from the left (NULL when there is none).
FULL OUTER JOIN	All rows from both tables, with NULLs wherever a side has no match.
CROSS JOIN	Every row from table A paired with every row from table B (Cartesian product).
SELF JOIN	A table joined to itself, used to compare rows within the same table.

Same data, different lens

Every join type above can be run against the exact same customers/orders tables. The data never changes — only the rule for which rows are kept and how missing matches are represented changes. Keeping that in mind makes the differences between join types much easier to remember.

Joins let you query across normalized tables as if the data lived in one place.
The ON clause defines how rows from each table are related.
This section reuses one customers/orders schema so you can compare join types directly.
Some rows are deliberately unmatched (Dave has no orders, order 105 has no customer) to illustrate how each join type behaves differently.