JOINs

Why JOINs Matter

Relational databases split data into multiple tables. JOINs let you combine rows from those tables in a single query.

Without JOINs you would:

Run multiple queries.
Fetch data separately.
Combine it in your application code.

With JOINs the database does the combination for you, which is:

Faster, because the database is optimized for this.
Simpler, because your SQL directly describes what you want.

Throughout this chapter we will use a simple example schema.

Example Tables

Imagine a basic shop:

sql

CREATE TABLE customers (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    email       TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date  DATE NOT NULL,
    total       NUMERIC(10, 2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);
CREATE TABLE order_items (
    id          SERIAL PRIMARY KEY,
    order_id    INTEGER NOT NULL,
    product_id  INTEGER NOT NULL,
    quantity    INTEGER NOT NULL,
    price       NUMERIC(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id)
);
CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    price       NUMERIC(10, 2) NOT NULL
);

Some sample data:

sql

INSERT INTO customers (id, name, email) VALUES
(1, 'Alice', 'alice@example.com'),
(2, 'Bob',   'bob@example.com'),
(3, 'Cara',  'cara@example.com');
INSERT INTO orders (id, customer_id, order_date, total) VALUES
(1, 1, '2024-08-01', 120.00),  -- Alice
(2, 1, '2024-08-05',  50.00),  -- Alice
(3, 2, '2024-08-03',  75.00);  -- Bob
INSERT INTO products (id, name, price) VALUES
(1, 'Keyboard', 40.00),
(2, 'Mouse',    20.00),
(3, 'Monitor', 200.00);
INSERT INTO order_items (id, order_id, product_id, quantity, price) VALUES
(1, 1, 1, 1, 40.00),  -- order 1, keyboard
(2, 1, 2, 2, 20.00),  -- order 1, two mice
(3, 2, 2, 1, 20.00),  -- order 2, one mouse
(4, 3, 1, 1, 40.00);  -- order 3, keyboard

We will use these tables in JOIN examples.

Important rule: A JOIN always operates on two tables (or subqueries) at a time, and combines rows based on a condition, usually linking primary keys and foreign keys.

JOIN Syntax Basics

The general pattern:

sql

SELECT
    columns
FROM
    table1
    <JOIN TYPE> JOIN table2
        ON join_condition;

Key parts:

JOIN TYPE defines how rows are matched and which rows are kept.
ON defines the matching rule, often table1.id = table2.some_foreign_key.

You can assign aliases to tables to keep queries short:

sql

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

Using aliases is very common in real projects.

INNER JOIN

An INNER JOIN returns only rows where the join condition matches in both tables.

Think of it as:

"Give me only the pairs where both sides exist and match."

Simple INNER JOIN example

List all orders with their customer name:

sql

SELECT
    o.id           AS order_id,
    o.order_date,
    o.total,
    c.name         AS customer_name
FROM orders AS o
INNER JOIN customers AS c
    ON o.customer_id = c.id;

Result:

order_id	order_date	total	customer_name
1	2024-08-01	120.0	Alice
2	2024-08-05	50.0	Alice
3	2024-08-03	75.0	Bob

If a customer had no orders, that customer would not appear, because INNER JOIN only shows matches.

You can omit INNER:

sql

SELECT ...
FROM orders o
JOIN customers c
    ON o.customer_id = c.id;

By default, JOIN is INNER JOIN.

INNER JOIN across multiple tables

You can chain JOINs:

sql

SELECT
    c.name          AS customer_name,
    o.id            AS order_id,
    p.name          AS product_name,
    oi.quantity,
    oi.price
FROM customers c
JOIN orders o
    ON c.id = o.customer_id
JOIN order_items oi
    ON o.id = oi.order_id
JOIN products p
    ON oi.product_id = p.id;

This returns every order item, with customer and product information.

LEFT JOIN

A LEFT JOIN returns all rows from the left table, and the matching rows from the right table. If there is no match, the right side columns are NULL.

Think of it as:

"Give me everything on the left, and what matches on the right, or NULL if there is no match."

LEFT JOIN example

List all customers and their orders, even if some customers have no orders:

sql

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

Result (with our sample data):

customer_name	order_id	total
Alice	1	120.0
Alice	2	50.0
Bob	3	75.0
Cara	NULL	NULL

Cara has no orders, but still appears. The order columns are NULL.

This is very useful when you want to:

See entries that might not have children (like customers without orders).
Start from a main table and show related data only if it exists.

LEFT JOIN with filtering

A common trap is filtering away your left-joined rows. For example, if you want customers who have no orders, you might write:

sql

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

Result:

name
Cara

The logic:

LEFT JOIN returns all customers.
Customers without orders get o.id = NULL.
WHERE o.id IS NULL keeps only those.

Important rule: With LEFT JOIN, to find rows that have no match in the right table, filter with WHERE right_table.column IS NULL.

If you mistakenly put conditions in the WHERE clause that require non-NULL right table columns, you might unintentionally turn a LEFT JOIN into something that behaves like an INNER JOIN. A safer pattern is to put such conditions inside the ON clause when you want to keep non-matching left rows.

Example of a subtle difference:

sql

-- Condition in WHERE: removes non-matching rows
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
WHERE o.total > 50;
-- Condition in ON: preserves non-matching rows
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id AND o.total > 50;

In the second query, customers with no orders still appear, because the LEFT JOIN keeps them and the condition is part of the join, not a filter afterward.

RIGHT JOIN

A RIGHT JOIN is the mirror of a LEFT JOIN.

It returns:

All rows from the right table.
Matching rows from the left.
NULLs for the left table when there is no match.

Many developers almost never use RIGHT JOIN, and instead simply switch the order of tables and use LEFT JOIN.

RIGHT JOIN example

List all customers and their orders using RIGHT JOIN:

sql

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

This gives the same result as the previous LEFT JOIN example.

In practice, you can choose either:

LEFT JOIN with customers on the left.
RIGHT JOIN with customers on the right.

Most codebases prefer LEFT JOIN only, because it is more common and easier to read.

FULL OUTER JOIN

A FULL OUTER JOIN returns:

All rows that appear in the left table.
All rows that appear in the right table.
Matching rows merged.
Non-matching rows filled with NULLs on the missing side.

Think of it as:

"Give me everything from both tables. If there is a match, combine them. If not, still show the row with NULLs on the other side."

Not all databases support FULL OUTER JOIN, but PostgreSQL does. Some systems emulate it with UNION.

FULL OUTER JOIN example

Imagine this scenario. We track:

customers who placed orders in our system.
marketing signups in a separate table.

sql

CREATE TABLE marketing_signups (
    email TEXT PRIMARY KEY,
    signup_date DATE NOT NULL
);
INSERT INTO marketing_signups (email, signup_date) VALUES
('alice@example.com', '2024-07-15'),
('dave@example.com',  '2024-08-02');  -- never ordered

Now we want:

People who ordered, even if they never signed up.
People who signed up, even if they never ordered.

sql

SELECT
    c.email AS customer_email,
    m.email AS marketing_email,
    c.name,
    m.signup_date
FROM customers c
FULL OUTER JOIN marketing_signups m
    ON c.email = m.email;

Result:

customer_email	marketing_email	name	signup_date
alice@example.com	alice@example.com	Alice	2024-07-15
bob@example.com	NULL	Bob	NULL
cara@example.com	NULL	Cara	NULL
NULL	dave@example.com	NULL	2024-08-02

Uses:

Comparing two datasets.
Finding differences between tables.
Data migration / reconciliation tasks.

FULL OUTER JOIN is less common in backend application code, but very handy for analytics and maintenance scripts.

CROSS JOIN

A CROSS JOIN returns the Cartesian product of the two tables, which means every row from the first table combined with every row from the second.

If table A has $m$ rows and table B has $n$ rows, the result has $m \times n$ rows.

Important rule: CROSS JOIN multiplies row counts. Use it carefully, especially with large tables, to avoid huge result sets.

CROSS JOIN example

Generate all combinations of customers and products, for example to create a price list or recommendation seeds.

sql

SELECT
    c.name      AS customer_name,
    p.name      AS product_name
FROM customers c
CROSS JOIN products p
ORDER BY c.name, p.name;

Result:

customer_name	product_name
Alice	Keyboard
Alice	Monitor
Alice	Mouse
Bob	Keyboard
Bob	Monitor
Bob	Mouse
Cara	Keyboard
Cara	Monitor
Cara	Mouse

CROSS JOIN is rarely used in normal CRUD queries, but it is useful when:

You need to generate combinations.
You create test data.
You do specific analytical tasks.

Note: In some dialects, you can also get the same effect with:

sql

FROM customers c, products p

but this style is discouraged in modern SQL in favor of explicit CROSS JOIN.

JOIN Conditions and ON vs USING

The most common join condition links:

A primary key in one table.
A foreign key in another.

General form:

sql

... JOIN table2
    ON table1.id = table2.table1_id;

Typical PK / FK example

Orders to customers:

sql

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

Multiple conditions

You can have multiple conditions:

sql

SELECT
    ...
FROM table1 t1
JOIN table2 t2
    ON t1.a = t2.a
   AND t1.b = t2.b;

USING clause

Some databases (such as PostgreSQL) support USING when the column names are the same in both tables:

sql

SELECT
    ...
FROM table1
JOIN table2 USING (id);

This is equivalent to:

sql

... JOIN table2
    ON table1.id = table2.id;

And the column id appears only once in the result.

For beginners, using ON explicitly is clearer, because it shows exactly which columns you match.

Self JOIN

A self join is a join of a table with itself.

Use it when:

Rows in a table reference other rows in the same table.
You want to compare rows within the same table.

Example: employees and managers

Consider:

sql

CREATE TABLE employees (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    manager_id  INTEGER REFERENCES employees(id)
);
INSERT INTO employees (id, name, manager_id) VALUES
(1, 'CEO',    NULL),
(2, 'Alice',  1),
(3, 'Bob',    1),
(4, 'Cara',   2);

We want each employee with their manager name:

sql

SELECT
    e.name       AS employee,
    m.name       AS manager
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.id
ORDER BY e.id;

Result:

employee	manager
CEO	NULL
Alice	CEO
Bob	CEO
Cara	Alice

The aliases e and m are essential, since both references point to the same table.

Self joins will appear when you model:

Organization structures.
Hierarchies.
Parent / child relationships in one table.

Joining More Than Two Tables

In real backend applications, queries often join several tables at once.

You can chain multiple JOINs:

sql

SELECT
    c.name              AS customer_name,
    o.id                AS order_id,
    o.order_date,
    SUM(oi.quantity)    AS total_items
FROM customers c
JOIN orders o
    ON c.id = o.customer_id
JOIN order_items oi
    ON o.id = oi.order_id
GROUP BY
    c.name, o.id, o.order_date
ORDER BY
    c.name, o.order_date;

Result:

customer_name	order_id	order_date	total_items
Alice	1	2024-08-01	3
Alice	2	2024-08-05	1
Bob	3	2024-08-03	1

You can mix join types:

sql

SELECT
    c.name,
    o.id AS order_id,
    SUM(oi.quantity) AS items
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
LEFT JOIN order_items oi
    ON o.id = oi.order_id
GROUP BY c.name, o.id
ORDER BY c.name, o.id;

This keeps customers even if they have no orders or items.

Practical JOIN Patterns for Backends

Here are some very common patterns you will use when writing backend code.

1. Loading related entity details

You need order details and customer info in a single API response:

sql

SELECT
    o.id,
    o.order_date,
    o.total,
    c.id   AS customer_id,
    c.name AS customer_name
FROM orders o
JOIN customers c
    ON o.customer_id = c.id
WHERE o.id = 1;

Your backend then converts this row into a JSON response.

2. Paginated list with search

List orders, filter by customer name, support pagination:

sql

SELECT
    o.id,
    o.order_date,
    o.total,
    c.name AS customer_name
FROM orders o
JOIN customers c
    ON o.customer_id = c.id
WHERE c.name ILIKE '%ali%'          -- search
ORDER BY o.order_date DESC
LIMIT 10 OFFSET 0;                  -- pagination

3. Checking ownership

For authorization, you confirm that a resource belongs to a user:

sql

SELECT 1
FROM orders o
JOIN customers c
    ON o.customer_id = c.id
WHERE o.id = $1
  AND c.id = $2;

If this returns a row, the order belongs to the customer.

4. Aggregations with JOINs

Total spend per customer:

sql

SELECT
    c.id,
    c.name,
    COALESCE(SUM(o.total), 0) AS total_spent
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY total_spent DESC;

COALESCE replaces NULL with 0 for customers without orders.

Summary

INNER JOIN: keep only matching rows from both tables.
LEFT JOIN: keep all rows from the left table, fill missing right rows with NULL.
RIGHT JOIN: mirror of LEFT JOIN, rarely needed if you reorder tables.
FULL OUTER JOIN: keep all rows from both tables, match where possible, fill with NULL otherwise.
CROSS JOIN: all combinations of rows from both tables, use with care.
Join conditions usually connect primary keys to foreign keys with ON.
LEFT JOIN with WHERE right.column IS NULL is a common way to find "no related record" cases.
Self joins let you connect a table to itself for hierarchies and comparisons.
Multiple JOINs are normal in real backend queries.

Understanding JOINs is essential for building any non-trivial backend that uses SQL databases.

JOINs

Why JOINs Matter

Example Tables

JOIN Syntax Basics

INNER JOIN

Simple INNER JOIN example

INNER JOIN across multiple tables

LEFT JOIN

LEFT JOIN example

LEFT JOIN with filtering

RIGHT JOIN

RIGHT JOIN example

FULL OUTER JOIN

FULL OUTER JOIN example

CROSS JOIN

CROSS JOIN example

JOIN Conditions and ON vs USING

Typical PK / FK example

Multiple conditions

USING clause

Self JOIN

Example: employees and managers

Joining More Than Two Tables

Practical JOIN Patterns for Backends

1. Loading related entity details

2. Paginated list with search

3. Checking ownership

4. Aggregations with JOINs

Summary

Comments

Where to Move