Module F-5·23 min read

INNER JOIN, LEFT JOIN, FULL OUTER JOIN, self-joins, and multi-table queries — the most feared concept made simple.

F-5 — Connecting Tables — Joins Demystified

Q: A software architect is designing a new microservice that interacts with a highly normalized relational database. They are considering denormalizing some data within the microservice's local cache to improve read performance. What is the primary architectural trade-off introduced by the *necessity* of using JOINs in a normalized schema, which this architect is implicitly trying to mitigate?

B. Higher CPU utilization on the database server for query execution. — The lesson explains that normalization splits data into separate tables, and 'Joins let you reconstruct the combined view when you need it.' This reconstruction process, involving combining data from multiple tables, is computationally intensive for the database server, consuming CPU and I/O resources. Therefore, the primary trade-off for read operations in a normalized schema is the increased CPU utilization and latency due to join execution (B). Denormalizing in a cache aims to pre-join or duplicate this data to avoid repeated database join costs. Options A and C are benefits of normalization (reduced redundancy, improved integrity), not costs. Option D, while writes might involve multiple tables, the primary performance concern mitigated by denormalization for reads is the join overhead.

Who this module is for: You completed F-4 and understand aggregation. Now you need to combine data from multiple tables — the foundational skill that makes relational databases genuinely powerful. Joins confuse almost every beginner because they require a new mental model. This module builds that model step by step.

Why Data Lives in Multiple Tables

Imagine a simple online store. You could store everything in one table:

text

orders_messy:
  order_id | customer_name | customer_email | product_name | product_price | quantity
  1        | Alice         | alice@...      | Keyboard     | 129.99        | 1
  2        | Alice         | alice@...      | Mouse        | 49.99         | 2
  3        | Bob           | bob@...        | Keyboard     | 129.99        | 1

This has problems:

Alice's email is stored twice — if she changes it, you must update multiple rows
The Keyboard's price is stored twice — if it changes, you must update multiple rows
Adding a new customer who has not ordered yet is impossible without a fake order
Removing all orders for a customer accidentally removes their account

The solution: split the data into separate tables, each representing one concept, and connect them with foreign keys.

text

customers:                    products:
  id | name  | email          id | name     | price
  1  | Alice | alice@...      1  | Keyboard | 129.99
  2  | Bob   | bob@...        2  | Mouse    | 49.99

orders:
  id | customer_id | product_id | quantity
  1  | 1           | 1          | 1        ← Alice bought Keyboard
  2  | 1           | 2          | 2        ← Alice bought Mouse
  3  | 2           | 1          | 1        ← Bob bought Keyboard

Now Alice's email lives in one place. The Keyboard's price lives in one place. Joins let you reconstruct the combined view when you need it.

Setting Up the Example Schema

Create these tables to follow along:

sql

CREATE DATABASE store;
\c store

CREATE TABLE customers (
  id    BIGSERIAL   PRIMARY KEY,
  name  TEXT        NOT NULL,
  email TEXT        NOT NULL UNIQUE,
  city  TEXT
);

CREATE TABLE products (
  id       BIGSERIAL      PRIMARY KEY,
  name     TEXT           NOT NULL,
  price    NUMERIC(10,2)  NOT NULL,
  category TEXT           NOT NULL
);

CREATE TABLE orders (
  id          BIGSERIAL   PRIMARY KEY,
  customer_id BIGINT      REFERENCES customers(id),
  product_id  BIGINT      REFERENCES products(id),
  quantity    INTEGER     NOT NULL,
  ordered_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO customers (name, email, city) VALUES
  ('Alice',  'alice@example.com', 'New York'),
  ('Bob',    'bob@example.com',   'London'),
  ('Carol',  'carol@example.com', 'Berlin'),
  ('David',  'david@example.com', NULL);       -- David has no city yet

INSERT INTO products (name, price, category) VALUES
  ('Mechanical Keyboard', 129.99, 'peripherals'),
  ('Wireless Mouse',       49.99, 'peripherals'),
  ('Monitor Stand',        39.99, 'accessories'),
  ('USB-C Hub',            64.99, 'accessories'),
  ('Laptop Sleeve',        24.99, 'accessories');

INSERT INTO orders (customer_id, product_id, quantity) VALUES
  (1, 1, 1),   -- Alice bought Keyboard
  (1, 2, 2),   -- Alice bought Mouse (2x)
  (2, 1, 1),   -- Bob bought Keyboard
  (3, 3, 1),   -- Carol bought Monitor Stand
  (3, 4, 1);   -- Carol bought USB-C Hub
-- Note: David (id=4) has no orders
-- Note: Laptop Sleeve (id=5) has no orders

`INNER JOIN` — Only Matching Rows

An INNER JOIN returns rows that have a match in both tables. If a customer has no orders, they do not appear. If an order references a customer that doesn't exist, it doesn't appear.

sql

SELECT customers.name, orders.quantity, products.name AS product
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
INNER JOIN products  ON orders.product_id  = products.id;

--  name  | quantity |       product
-- -------+----------+---------------------
--  Alice |        1 | Mechanical Keyboard
--  Alice |        2 | Wireless Mouse
--  Bob   |        1 | Mechanical Keyboard
--  Carol |        1 | Monitor Stand
--  Carol |        1 | USB-C Hub

Notice:

David does not appear — he has no orders
Laptop Sleeve does not appear — nobody ordered it

The syntax:

sql

SELECT [columns]
FROM table_a
INNER JOIN table_b ON table_a.column = table_b.column;

-- 'INNER' is optional — bare JOIN means INNER JOIN
SELECT [columns]
FROM table_a
JOIN table_b ON table_a.column = table_b.column;

Using aliases to shorten table names:

sql

SELECT c.name, o.quantity, p.name AS product
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products  p ON o.product_id  = p.id;

`LEFT JOIN` — Keep All Left Rows

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

sql

-- List ALL customers, and their orders if they have any
SELECT c.name, o.quantity, p.name AS product
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN products p ON o.product_id = p.id
ORDER BY c.name;

--  name  | quantity |       product
-- -------+----------+---------------------
--  Alice |        1 | Mechanical Keyboard
--  Alice |        2 | Wireless Mouse
--  Bob   |        1 | Mechanical Keyboard
--  Carol |        1 | Monitor Stand
--  Carol |        1 | USB-C Hub
--  David |   (null) |              (null)  ← David has no orders

David appears with NULL values for the order columns because he has no orders — but he is not excluded. This is the key difference from INNER JOIN.

The most common use of LEFT JOIN: finding rows that have NO match.

sql

-- Find customers who have never placed an order
SELECT c.name, c.email
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;   -- NULL means no matching order was found

--  name  |       email
-- -------+------------------
--  David | david@example.com

sql

-- Find products that have never been ordered
SELECT p.name, p.price
FROM products p
LEFT JOIN orders o ON p.id = o.product_id
WHERE o.id IS NULL;

--      name      | price
-- ---------------+-------
--  Laptop Sleeve | 24.99

`RIGHT JOIN` — Keep All Right Rows

RIGHT JOIN is the mirror of LEFT JOIN — it keeps all rows from the right table. It is used rarely in practice because you can always rewrite a RIGHT JOIN as a LEFT JOIN by swapping the table order.

sql

-- These two queries produce identical results:
SELECT c.name, o.quantity
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id;

-- ← equivalent to →

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

Use LEFT JOIN consistently and avoid RIGHT JOIN — it makes queries easier to read.

`FULL OUTER JOIN` — Keep All Rows from Both Sides

FULL OUTER JOIN returns all rows from both tables. Where there is no match, the missing side has NULLs.

sql

-- All customers AND all products, showing orders where they connect
SELECT c.name AS customer, p.name AS product, o.quantity
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id
FULL OUTER JOIN products p ON o.product_id = p.id
ORDER BY c.name;

-- Result includes:
-- - Customers with orders (matched)
-- - David (customer with no orders)
-- - Laptop Sleeve (product with no orders)

Used less frequently than INNER and LEFT joins, but useful for finding unmatched rows on either side.

`CROSS JOIN` — Every Combination

A CROSS JOIN produces the Cartesian product — every row in the left table combined with every row in the right table.

sql

-- 4 customers × 5 products = 20 rows
SELECT c.name, p.name AS product
FROM customers c
CROSS JOIN products p;

This is rarely intentional. If you write a JOIN without an ON clause, you accidentally get a cross join — a common beginner mistake.

sql

-- ❌ ACCIDENTAL cross join (missing ON clause)
SELECT c.name, o.quantity
FROM customers c
JOIN orders o;      -- Missing: ON c.id = o.customer_id
-- Returns 4 customers × 5 orders = 20 rows (wrong!)

-- ✅ CORRECT
SELECT c.name, o.quantity
FROM customers c
JOIN orders o ON c.id = o.customer_id;

Self-Joins — A Table Joining Itself

Sometimes you need to join a table to itself. The classic example: an employee table where each employee has a manager_id that references another employee's id.

sql

CREATE TABLE employees (
  id         BIGSERIAL PRIMARY KEY,
  name       TEXT      NOT NULL,
  manager_id BIGINT    REFERENCES employees(id)  -- references same table
);

INSERT INTO employees (name, manager_id) VALUES
  ('CEO',        NULL),   -- id=1, no manager
  ('VP Sales',   1),      -- id=2, reports to CEO
  ('VP Eng',     1),      -- id=3, reports to CEO
  ('Sales Rep',  2),      -- id=4, reports to VP Sales
  ('Engineer',   3);      -- id=5, reports to VP Eng

-- Show each employee with their manager's name
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;

--  employee  |  manager
-- -----------+----------
--  CEO       |  (null)     ← CEO has no manager
--  VP Sales  |  CEO
--  VP Eng    |  CEO
--  Sales Rep |  VP Sales
--  Engineer  |  VP Eng

The key: you use aliases (e and m) to distinguish the two "instances" of the same table.

Joining Three or More Tables

Joins chain naturally — each new JOIN adds another table:

sql

-- Full order details: customer name, product name, price, quantity, total
SELECT
  c.name        AS customer,
  c.city        AS city,
  p.name        AS product,
  p.price       AS unit_price,
  o.quantity,
  p.price * o.quantity AS line_total,
  o.ordered_at
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products  p ON o.product_id  = p.id
ORDER BY o.ordered_at;

Common Join Mistakes

Mistake 1: Duplicate rows from one-to-many joins

If a customer has 5 orders, joining customers to orders gives 5 rows for that customer. This is correct and expected — but surprises beginners who then aggregate incorrectly.

sql

-- ❌ WRONG: counting total orders per customer using GROUP BY without noticing duplicates
SELECT COUNT(*) FROM customers JOIN orders ON customers.id = orders.customer_id;
-- Returns 5 (number of orders), not 4 (number of customers)

-- ✅ CORRECT: that's exactly right — there are 5 orders
-- If you want unique customers who have ordered, use DISTINCT:
SELECT COUNT(DISTINCT c.id)
FROM customers c
JOIN orders o ON c.id = o.customer_id;
-- Returns 3 (Alice, Bob, Carol — David has no orders)

Mistake 2: Ambiguous column names

When two joined tables have a column with the same name, you must qualify which table's column you want:

sql

-- ❌ ERROR: 'id' is ambiguous — which table?
SELECT id, name FROM customers JOIN orders ON customers.id = orders.customer_id;

-- ✅ CORRECT: qualify with the table name
SELECT customers.id, customers.name FROM customers
JOIN orders ON customers.id = orders.customer_id;

-- ✅ ALSO CORRECT: use aliases
SELECT c.id, c.name FROM customers c
JOIN orders o ON c.id = o.customer_id;

Mistake 3: Missing the join condition

sql

-- ❌ This is a CROSS JOIN producing a huge result
SELECT * FROM customers, orders;  -- old-style implicit join syntax

-- ✅ CORRECT
SELECT * FROM customers JOIN orders ON customers.id = orders.customer_id;

Practical Exercise: Complete Store Queries

Using the store database with customers, products, and orders:

sql

-- Q1: Show every order with customer name, product name, and total cost
SELECT
  c.name AS customer,
  p.name AS product,
  o.quantity,
  ROUND(p.price * o.quantity, 2) AS total
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id
ORDER BY c.name, p.name;

-- Q2: Total amount spent by each customer
SELECT
  c.name,
  SUM(p.price * o.quantity) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN products p ON o.product_id = p.id
GROUP BY c.name
ORDER BY total_spent DESC;

-- Q3: Customers who have never ordered anything
SELECT c.name, c.email
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

-- Q4: Products that have been ordered at least once, and how many times
SELECT p.name, COUNT(o.id) AS order_count
FROM products p
JOIN orders o ON p.id = o.product_id
GROUP BY p.name
ORDER BY order_count DESC;

-- Q5: Products that have never been ordered
SELECT p.name, p.price
FROM products p
LEFT JOIN orders o ON p.id = o.product_id
WHERE o.id IS NULL;

-- Q6: Customers and the number of unique products they ordered
SELECT c.name, COUNT(DISTINCT o.product_id) AS unique_products
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name
ORDER BY unique_products DESC;

-- Q7: All customers and their total orders (including those with 0 orders)
SELECT
  c.name,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(p.price * o.quantity), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN products p ON o.product_id = p.id
GROUP BY c.name
ORDER BY total_spent DESC;

Join Type Summary

Join Type	Returns
`INNER JOIN`	Only rows that match in both tables
`LEFT JOIN`	All rows from left table + matches from right (NULLs where no match)
`RIGHT JOIN`	All rows from right table + matches from left (use LEFT JOIN instead)
`FULL OUTER JOIN`	All rows from both tables (NULLs where no match on either side)
`CROSS JOIN`	Every combination of left × right rows

When to use which:

Finding related data → INNER JOIN
Keeping all records even without a match → LEFT JOIN
Finding records with no match → LEFT JOIN ... WHERE right.id IS NULL
Comparing two complete sets → FULL OUTER JOIN

Module F-6 covers constraints — the rules that make your database trustworthy: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK.

Next: F-6 — Constraints and Data Integrity →

Knowledge Check

A software architect is designing a new microservice that interacts with a highly normalized relational database. They are considering denormalizing some data within the microservice's local cache to improve read performance. What is the primary architectural trade-off introduced by the *necessity* of using JOINs in a normalized schema, which this architect is implicitly trying to mitigate?

Test your knowledge with more question sets

Question 2: A data analyst reports that a dashboard showing 'Total Revenue by Customer' is displaying NULL or 0 for some customers, even though the customers table clearly lists them. The SQL query used for the dashboard is:

sql

SELECT c.name, SUM(o.quantity * p.price) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN products p ON o.product_id = p.id
GROUP BY c.name;

As a Senior Principal Software Engineer, what is the most likely reason for this discrepancy and the correct immediate fix to include all customers, showing 0 for those with no orders?

A) A. The products table has missing price data, causing NULLs in the SUM. The fix is to add a WHERE p.price IS NOT NULL clause.
B) B. The JOIN between customers and orders is an INNER JOIN, which excludes customers without matching orders. The fix is to change it to a LEFT JOIN and use COALESCE for SUM.
C) C. The GROUP BY clause is incorrect and should include o.id to ensure all orders are counted. The fix is to add o.id to GROUP BY.
D) D. The SUM function implicitly handles NULLs as 0, so the issue must be that quantity or price are 0. The fix is to check the orders and products data.

Reveal Answer

Correct Answer: B

The lesson explicitly states that INNER JOIN (which JOIN defaults to) 'returns rows that have a match in both tables' and demonstrates that customers without orders (like David) 'do not appear' in the result. The problem describes customers being entirely missing or showing NULL/0 revenue, which is characteristic of INNER JOIN excluding them. The correct fix is to use a LEFT JOIN from customers to orders to ensure all customers are included. For customers with no orders, the orders and products columns will be NULL, causing SUM(o.quantity * p.price) to evaluate to NULL. To display 0 instead of NULL for these customers, COALESCE(SUM(o.quantity * p.price), 0) should be used. Option A is incorrect because INNER JOIN would already filter out rows where price is NULL if it's part of the join or calculation, but the primary issue is missing customers. Option C is incorrect; GROUP BY c.name is appropriate for summing per customer. Option D is incorrect; while SUM ignores NULLs, the core problem is the exclusion of entire customer rows, not just NULL values within existing rows.

Question 3: During a peak traffic event, a critical reporting service experiences severe database load, leading to timeouts and degraded user experience. Upon investigation, a newly deployed SQL query for a 'product compatibility matrix' report is identified as the culprit. The query looks like this:

sql

SELECT p1.name AS product_a, p2.name AS product_b
FROM products p1, products p2; -- Old-style implicit join

Assuming the products table has N rows, what is the primary performance issue this query introduces, and what is the most direct consequence in a production environment?

A) A. It creates an N*N Cartesian product, leading to excessive memory usage and network transfer for the result set.
B) B. It causes a deadlock because p1 and p2 are aliases of the same table, leading to contention.
C) C. It results in an N row output, but the database optimizer struggles with the implicit join syntax, causing slow execution.
D) D. It performs an INNER JOIN by default, but without an ON clause, it scans the entire table multiple times, leading to high I/O.

Reveal Answer

Correct Answer: A

The lesson explicitly warns that 'If you write a JOIN without an ON clause, you accidentally get a cross join' and highlights SELECT * FROM customers, orders; as an 'old-style implicit join syntax' that is an 'ACCIDENTAL cross join.' A CROSS JOIN produces a Cartesian product, meaning every row from the first table is combined with every row from the second table. If the products table has N rows, joining it to itself this way will generate N * N rows. For even moderately sized tables (e.g., N=10,000), this results in 100 million rows, which can quickly overwhelm database memory, CPU, and network bandwidth, leading to the observed timeouts and degraded performance (A). Option B is incorrect; self-joins with aliases are a standard and safe practice and do not inherently cause deadlocks. Option C is incorrect; the output is N*N rows, not N. Option D is incorrect; while it is an implicit CROSS JOIN, the primary issue is the massive result set size, not just multiple table scans, and it's not an INNER JOIN in the sense of matching rows.

PreviousModule F-4: Aggregation — Summarising Your Data Next Module F-6: Constraints and Data Integrity

Discussion

Join the discussion

Loading comments...