PostgreSQL Multi-Table Join Queries
1. What You'll Learn
- INNER JOIN (inner join)
- LEFT / RIGHT / FULL OUTER JOIN (outer joins)
- CROSS JOIN (cross join)
- NATURAL JOIN (use with caution)
- Self-join
- USING shorthand syntax
- Multi-table joins (3+ tables)
- PostgreSQL feature: LATERAL JOIN
- JOIN performance basics (EXPLAIN primer)
2. The Story
Charlie is a backend engineer at a SaaS platform. The product manager asks him to generate a user purchase detail report that needs to combine 4 tables:
- users — user information
- orders — orders
- order_items — order line items
- products — products
A user may have multiple orders; each order has multiple items; each item maps to one product. Charlie must pick the right JOIN type to ensure no order-less users are dropped and no unexpected Cartesian product is created.
3. Concept
(1) JOIN Type Overview
| JOIN type | Meaning | Which side retained | Typical scenario |
|---|---|---|---|
| INNER JOIN | Keep only matching rows | Neither side | Require a match to include |
| LEFT JOIN | Keep all of left table | Left | Main table + optional related rows |
| RIGHT JOIN | Keep all of right table | Right | Rarely used |
| FULL JOIN | Keep all of both sides | Both | Find differences / reconcile |
| CROSS JOIN | Cartesian product | Unconditional | Permutations / combinations |
| LATERAL JOIN | Subquery references left table | — | Top-N per row |
flowchart LR
subgraph Inner
A1((A)) --- B1((B))
end
subgraph Left
A2((A)) --- B2((B))
A3((A)) -.-> B3((∅))
end
subgraph Full
A4((A)) --- B4((B))
A5((A)) -.-> B6((∅))
B5((∅)) -.-> A6((A))
end
style A1 fill:#c8e6c9
style B1 fill:#c8e6c9
style A2 fill:#c8e6c9
style A3 fill:#c8e6c9
style B2 fill:#c8e6c9
style A4 fill:#c8e6c9
style A5 fill:#c8e6c9
style B4 fill:#c8e6c9
(2) INNER JOIN
Returns only the rows that match in both tables.
▶ Example: Query Users Who Have Orders
SQL
SELECT
u.user_id,
u.name,
o.order_id,
o.amount
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;
TEXT
user_id | name | order_id | amount
---------+-------+----------+--------
1 | Alice | 101 | 15000
1 | Alice | 102 | 8000
2 | Bob | 201 | 25000
Users who have never placed an order do not appear.
(3) LEFT JOIN
Keeps all rows of the left table; fills NULL on the right when there is no match.
▶ Example: Include Order-less Users
SQL
SELECT
u.user_id,
u.name,
o.order_id,
o.amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;
TEXT
user_id | name | order_id | amount
---------+---------+----------+--------
1 | Alice | 101 | 15000
1 | Alice | 102 | 8000
2 | Bob | 201 | 25000
3 | Charlie | |
Charlie has no orders, so order_id and amount are NULL.
▶ Example: Find Order-less Users
SQL
SELECT u.user_id, u.name
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL;
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
(4) RIGHT JOIN and FULL JOIN
▶ Example: FULL JOIN to Find Differences Between Users and Orders
SQL
SELECT
u.user_id,
u.name,
o.order_id,
o.user_id AS order_user_id
FROM users u
FULL JOIN orders o ON u.user_id = o.user_id
ORDER BY u.user_id NULLS LAST, o.order_id NULLS LAST;
TEXT
user_id | name | order_id | order_user_id
---------+---------+----------+---------------
1 | Alice | 101 | 1
2 | Bob | 201 | 2
3 | Charlie | |
| | 999 | 99
The order with user_id=99 has no matching user, and the user with user_id=3 has no order—both differences are visible.
▶ Example: RIGHT JOIN to View Orphan Orders
SQL
SELECT o.order_id, o.user_id, u.name
FROM users u
RIGHT JOIN orders o ON u.user_id = o.user_id
WHERE u.user_id IS NULL;
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
| JOIN type | Behavior | Use comparison |
|---|---|---|
| LEFT JOIN | Retain all of left table | Query main + related; find left-side gaps |
| RIGHT JOIN | Retain all of right table | Can be rewritten as LEFT JOIN with swapped sides |
| FULL JOIN | Retain all of both sides | Reconciliation, finding differences |
(5) CROSS JOIN and NATURAL JOIN
▶ Example: CROSS JOIN Generates All Combinations
SQL
SELECT
d.department_name,
p.project_name
FROM departments d
CROSS JOIN projects p;
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
3 departments × 5 projects = 15 rows.
▶ Example: NATURAL JOIN (use with caution)
SQL
SELECT * FROM users
NATURAL JOIN orders;
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
NATURAL JOIN joins automatically on columns with the same name in both tables. Dangerous: if the two tables share multiple identically-named columns (e.g., created_at), it silently produces extra AND conditions. Prefer an explicit ON or USING.
| Syntax | Pros | Cons |
|---|---|---|
| NATURAL JOIN | Terse | A column-name change may alter join logic |
| USING(col) | Terse and explicit | Only for equi-joins on same-named columns |
| ON a.col = b.col | Full control | Verbose |
(6) USING Syntax
▶ Example: USING in Place of ON
SQL
SELECT
u.name,
o.order_id,
o.amount
FROM users u
JOIN orders o USING (user_id);
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
USING merges same-named columns into one; SELECT * does not output user_id twice.
(7) Self-Join
A table joined with itself, useful for hierarchies or same-table comparisons.
▶ Example: Employee–Manager Pairing
SQL
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
TEXT
employee | manager
----------+---------
Alice | David
Bob | Alice
Charlie | Alice
David |
▶ Example: Compare Prices of Products in the Same Category
SQL
SELECT
a.product_name AS product_a,
b.product_name AS product_b,
a.price - b.price AS price_diff
FROM products a
JOIN products b ON a.category_id = b.category_id
AND a.product_id < b.product_id
AND ABS(a.price - b.price) < 100;
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
4. Key Points
(1) Multi-Table Joins (3+ Tables)
Charlie's requirement: join users → orders → order_items → products.
▶ Example: 4-Table User Purchase Detail
SQL
SELECT
u.name AS user_name,
o.order_id,
o.created_at AS order_date,
p.product_name,
oi.quantity,
oi.unit_price,
oi.quantity * oi.unit_price AS line_total
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
ORDER BY u.name, o.order_id, oi.order_item_id;
TEXT
user_name | order_id | order_date | product_name | quantity | unit_price | line_total
-----------+----------+----------------------+--------------+----------+------------+------------
Alice | 101 | 2025-03-15 10:30:00 | Widget Pro | 2 | 15000 | 30000
Alice | 101 | 2025-03-15 10:30:00 | Gadget Mini | 5 | 3000 | 15000
Alice | 102 | 2025-04-02 14:20:00 | Widget Pro | 1 | 15000 | 15000
Bob | 201 | 2025-05-10 09:00:00 | Server Rack | 1 | 80000 | 80000
(2) LATERAL JOIN (PostgreSQL Feature)
LATERAL lets a subquery reference columns from the left table—in effect, the subquery runs once per row of the left table.
▶ Example: Each User's 3 Most Recent Orders
SQL
SELECT
u.name,
recent.order_id,
recent.amount,
recent.created_at
FROM users u
LEFT JOIN LATERAL (
SELECT o.order_id, o.amount, o.created_at
FROM orders o
WHERE o.user_id = u.user_id
ORDER BY o.created_at DESC
LIMIT 3
) recent ON true
ORDER BY u.name, recent.created_at DESC;
Output:
TEXT
CREATE TABLE
| Approach | Can reference left table | Top-N support | Performance |
|---|---|---|---|
| Plain subquery | No | Needs ROW_NUMBER window | One scan |
| LATERAL | Yes | Direct LIMIT | Subquery runs per row |
| Window function | — | ROW_NUMBER + filter | One scan |
▶ Example: Each Product's Latest Review
SQL
SELECT
p.product_name,
r.review_text,
r.created_at
FROM products p
LEFT JOIN LATERAL (
SELECT review_text, created_at
FROM reviews r
WHERE r.product_id = p.product_id
ORDER BY created_at DESC
LIMIT 1
) r ON true;
Output:
TEXT
CREATE TABLE
(3) JOIN Performance Basics
▶ Example: EXPLAIN to View the Join Plan
SQL
EXPLAIN
SELECT u.name, o.order_id
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE o.amount > 50000;
TEXT
Hash Join
Hash Cond: (o.user_id = u.user_id)
-> Seq Scan on orders
Filter: (amount > 50000)
-> Hash
-> Seq Scan on users
| JOIN strategy | When to use | Characteristics |
|---|---|---|
| Nested Loop | Small table drives large table | Good for indexed equi-conditions |
| Hash Join | Equi-join, no index | Builds a hash table; preferred at large volumes |
| Merge Join | Already-sorted data | Needs both sides sorted |
▶ Example: Slow Query from a Missing Index
SQL
EXPLAIN ANALYZE
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.email = o.customer_email;
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
If the email column is not indexed, it may degrade to a Nested Loop full-table scan.
▶ Example: Add an Index to Speed Up the JOIN
SQL
CREATE INDEX idx_orders_user_id ON orders(user_id);
Output:
TEXT
CREATE TABLE
5. Practice
▶ Example: LEFT JOIN to Count Orders per User (Including 0)
SQL
SELECT
u.user_id,
u.name,
COUNT(o.order_id) AS order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.name
ORDER BY order_count DESC;
Output:
TEXT
count
-------
5
(1 row)
▶ Example: FULL JOIN to Reconcile Two Systems' Users
SQL
SELECT
a.user_id AS system_a_id,
a.email AS system_a_email,
b.user_id AS system_b_id,
b.email AS system_b_email
FROM system_a_users a
FULL JOIN system_b_users b ON a.email = b.email
ORDER BY a.user_id NULLS LAST, b.user_id NULLS LAST;
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
▶ Example: Self-Join to Find Users Who Ordered on the Same Day
SQL
SELECT DISTINCT
a.name AS user_a,
b.name AS user_b
FROM orders oa
JOIN users a ON oa.user_id = a.user_id
JOIN orders ob ON DATE(oa.created_at) = DATE(ob.created_at)
JOIN users b ON ob.user_id = b.user_id
WHERE a.user_id < b.user_id;
Output:
TEXT
CREATE TABLE
6. Comprehensive Example
Charlie's user purchase detail report—4-table join + LATERAL for recent orders:
SQL
SELECT
u.name AS user_name,
u.email,
coalesce(order_summary.total_orders, 0) AS total_orders,
coalesce(order_summary.total_spent, 0) AS total_spent,
recent.order_id AS latest_order_id,
recent.created_at AS latest_order_date,
recent.amount AS latest_amount
FROM users u
LEFT JOIN LATERAL (
SELECT
COUNT(*) AS total_orders,
SUM(amount) AS total_spent
FROM orders o
WHERE o.user_id = u.user_id
) order_summary ON true
LEFT JOIN LATERAL (
SELECT order_id, created_at, amount
FROM orders o
WHERE o.user_id = u.user_id
ORDER BY created_at DESC
LIMIT 1
) recent ON true
ORDER BY total_spent DESC NULLS LAST;
TEXT
user_name | email | total_orders | total_spent | latest_order_id | latest_order_date | latest_amount
-----------+--------------------+--------------+-------------+-----------------+-----------------------+--------------
Bob | bob@example.com | 5 | 320000 | 205 | 2025-11-20 16:00:00 | 85000
Alice | alice@example.com | 3 | 180000 | 102 | 2025-04-02 14:20:00 | 15000
Charlie | charlie@example.com| 0 | 0 | | |
7. JOIN Selection Decision Tree
flowchart TD
A[Need to join multiple tables?] -->|No| Z[No JOIN needed]
A -->|Yes| B{Need to keep<br/>non-matching rows?}
B -->|No| C[INNER JOIN]
B -->|Yes, keep left| D[LEFT JOIN]
B -->|Yes, keep right| E[RIGHT JOIN]
B -->|Yes, keep both| F[FULL JOIN]
C --> G{Need Top-N<br/>or reference left columns?}
G -->|Yes| H[LATERAL JOIN]
G -->|No| I[Plain INNER JOIN]
D --> G
style H fill:#c8e6c9
style F fill:#fff9c4
❓ FAQ
Q Does filtering a right-table column with WHERE after a LEFT JOIN turn it into an INNER JOIN?
A Yes. WHERE o.col = 'x' filters out the right-table NULL rows, which is equivalent to an INNER JOIN. Move the condition into the ON clause instead.
Q Does the order of multi-table JOINs affect the result?
A The order of INNER JOINs does not affect the result (logically equivalent), but the order of LEFT JOINs does—the left side is the retained side and cannot be swapped freely.
Q What's the difference between USING and ON?
A USING(col) requires same-named columns and an equi-join, merging that column into a single output column; ON is more flexible, supporting different column names and complex conditions.
Q How does LATERAL differ from a subquery?
A A plain subquery cannot reference columns from the left table in the same FROM level; LATERAL can—it is effectively a subquery that runs once per left-table row.
Q What is CROSS JOIN actually useful for?
A Generating permutations (e.g., date × dimension), generating sequences, report matrices, and so on. Note that the result row count is the product of the two table sizes.
Q Why is NATURAL JOIN discouraged?
A It implicitly joins on all same-named columns; adding a same-named column changes the join logic and causes hard-to-trace bugs. Write ON or USING explicitly for safety.
📖 Summary
- INNER JOIN keeps matching rows only; LEFT JOIN keeps all of the left table
- RIGHT JOIN can be rewritten as a LEFT JOIN with swapped sides; FULL JOIN keeps both sides
- CROSS JOIN produces a Cartesian product; NATURAL JOIN's implicit join needs caution
- Self-join is used for hierarchies and same-table comparisons; give each table a distinct alias
- USING simplifies equi-joins on same-named columns; ON supports arbitrary conditions
- LATERAL JOIN can reference left-table columns, ideal for Top-N scenarios
- Join multiple tables level by level along relationships; mind the LEFT JOIN order
- EXPLAIN reveals the JOIN strategy; indexes are the key to performance
📝 Exercises
- ⭐ Write an INNER JOIN query joining orders and order_items, outputting the total quantity (SUM quantity) per order.
- ⭐ Use a LEFT JOIN to find products that have never been purchased (products LEFT JOIN order_items, filter for NULL).
- ⭐⭐ Join users → orders → order_items → products and output each user's purchase detail, including product name and line amount.
- ⭐⭐ Use a LATERAL JOIN to find the 2 highest-priced products in each product category.
- ⭐⭐⭐ Write a SQL statement using FULL JOIN to compare two systems' user tables (system_a_users / system_b_users), marking records that exist only in A, only in B, or in both, and count how many fall into each category.
