Joins and relationships_

Relational databases keep each entity in its own table and connect them through keys. A join combines rows from two tables by matching values, usually a foreign key on one side against a primary key on the other. This page covers how to model the two relationship shapes you'll meet constantly, and the join types you'll use in practice.

Setup

The examples below use two tables, customers and orders, where every order records which customer placed it. Create and seed them first:

CREATE TABLE customers (
    id   BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    total       DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers (id)
);

INSERT INTO customers (name) VALUES ('Ada'), ('Grace'), ('Linus');
INSERT INTO orders (customer_id, total) VALUES
    (1, 120.00), (1, 80.00), (2, 45.50);

Ada has two orders, Grace has one, and Linus has none.

What a join actually does

A join takes two tables and produces a new, temporary table built from them in three steps:

1. Pair rows from one side with rows from the other.
2. Evaluate the join condition on each pair, here o.customer_id = c.id.
3. Keep the pairs where it's true; each one becomes a result row carrying the columns of both sides.

customers               orders                        result of the join
id | name               id | customer_id | total     name  | total
 1 | Ada        ←──┬──   1 |           1 | 120.00    Ada   | 120.00
                   └──   2 |           1 |  80.00    Ada   |  80.00
 2 | Grace      ←─────   3 |           2 |  45.50    Grace |  45.50
 3 | Linus      (no order matches)

Two things fall out of this model:

• A row that matches several rows on the other side appears several times in the result: Ada shows up twice because two orders point at her.
• A row that matches nothing, like Linus, produces no pair at all, and what happens to such unmatched rows is exactly what distinguishes the join types below.

The database doesn't literally build every combination; indexes let it jump straight to matching rows, but the result is always as if it had.

One-to-many relationships

One customer has many orders, so orders carries a customer_id column referencing customers. The FOREIGN KEY clause makes the database reject any customer_id that doesn't exist in customers:

INSERT INTO orders (customer_id, total) VALUES (99, 10.00);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`your_db`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

A foreign key also decides what happens when the parent row is deleted. Declare it with the behavior you want:

• The default blocks deleting a customer who still has orders.
• ON DELETE CASCADE deletes the customer's orders along with the customer.
• ON DELETE SET NULL keeps the orders but clears their customer_id (the column must be nullable).

Inner joins

An inner join returns only rows that match on both sides:

SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.total;

name	total
Ada	80.00
Ada	120.00
Grace	45.50

Linus disappears from the result because he has no matching order. JOIN and INNER JOIN are the same thing.

Left joins

A left join keeps every row from the left table and fills the right side with NULL where nothing matches:

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.total;

name	total
Ada	80.00
Ada	120.00
Grace	45.50
Linus	NULL

This is the join for "all X, with their Y if any". Combined with aggregation it answers questions like order counts per customer, including zero:

SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY orders DESC;

Note COUNT(o.id), not COUNT(*): counting a column from the right table skips the NULL produced for Linus, yielding 0 instead of 1.

RIGHT JOIN mirrors LEFT JOIN in the other direction. In practice, most people reorder the tables and use LEFT JOIN for consistency.

No full joins

MySQL does not support FULL OUTER JOIN. When you need unmatched rows from both sides, such as reconciling two datasets, combine a left and a right join with UNION:

CREATE TABLE imported_customers (id BIGINT, name VARCHAR(100));
INSERT INTO imported_customers VALUES (1, 'Ada'), (4, 'Margaret');

SELECT c.name AS in_database, i.name AS in_import
FROM customers c
LEFT JOIN imported_customers i ON i.id = c.id
UNION
SELECT c.name, i.name
FROM customers c
RIGHT JOIN imported_customers i ON i.id = c.id;

in_database	in_import
Ada	Ada
Grace	NULL
Linus	NULL
NULL	Margaret

Note that UNION removes duplicate result rows. If your data can legitimately contain rows that project to identical values, use UNION ALL and restrict the second branch to right-side-only rows (WHERE c.id IS NULL) so nothing is collapsed.

Cross joins

A cross join written without any join condition produces every combination of rows from both tables. (In MySQL the CROSS JOIN keyword also accepts an ON clause, which turns it into an ordinary inner join; it's the absence of a condition that makes it Cartesian.) Use it to generate combinations, such as every product in every size:

CREATE TABLE sizes (size CHAR(1));
INSERT INTO sizes VALUES ('S'), ('M'), ('L');

SELECT c.name, s.size
FROM customers c
CROSS JOIN sizes s
ORDER BY c.name, s.size;

Three customers times three sizes returns nine rows. Be careful with large tables: the result size is the product of both row counts.

Anti-joins: rows without a match

"Customers with no orders" is an anti-join. Two equivalent spellings:

SELECT name FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Both return only Linus. Prefer NOT EXISTS over NOT IN (SELECT ...): if the subquery ever returns a NULL, NOT IN returns no rows at all, and nothing reports an error.

Many-to-many relationships

When both sides can have many of the other, such as orders and products, neither table can hold the foreign key. A junction table holds one row per connection:

CREATE TABLE products (
    id   BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL
);

CREATE TABLE order_items (
    order_id   BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity   INT NOT NULL DEFAULT 1,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders (id),
    FOREIGN KEY (product_id) REFERENCES products (id)
);

INSERT INTO products (name) VALUES ('Keyboard'), ('Mouse');
INSERT INTO order_items (order_id, product_id, quantity) VALUES
    (1, 1, 1), (1, 2, 2), (2, 2, 1);

The composite primary key (order_id, product_id) prevents duplicate connections, and the junction table is the natural home for relationship attributes like quantity. Traversing the relationship is two joins:

SELECT o.id AS order_id, p.name, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
ORDER BY o.id, p.name;

order_id	name	quantity
1	Keyboard	1
1	Mouse	2
2	Mouse	1

Each result row is one line item: the first join finds the connections belonging to each order, and the second swaps each product_id for the product's actual name. The Mouse appears under both orders because two junction rows point at it, one connection per row.

Join or separate queries?

Fetching a list and then querying once per row (the N+1 pattern) multiplies round trips and usually loses badly to a single join, even a large one. Let the database combine the data; that is what it is optimized for. If a join's result set explodes because one row matches thousands, paginate or aggregate on the many side instead of splitting into per-row queries.