F-5 — Connecting Tables — Joins Demystified

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

-- 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

-- 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.

-- 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.

-- 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.

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

This is rarely intentional. An explicit JOIN with no ON/USING clause is actually a syntax error in PostgreSQL — the grammar requires one. The accidental version comes from the old comma-join syntax instead, which silently is a cross join with no condition required:

-- ❌ ACCIDENTAL cross join (old-style comma join, no condition possible)
SELECT c.name, o.quantity
FROM customers c, orders o;      -- Missing: WHERE c.id = o.customer_id
-- Returns 4 customers × 5 orders = 20 rows (wrong!) — and it runs without error
 
-- ✅ 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.

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:

-- 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.

-- ❌ 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:

-- ❌ 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

-- ❌ 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:

-- 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

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

One join-performance trap worth knowing now: every join in this module leans on foreign-key columns like customer_id/product_id, but PostgreSQL does not automatically index foreign-key columns — only the referenced side (the primary/unique key being pointed at) gets an index for free. Without an explicit index on the FK column itself, joins and cascading deletes on the child table can end up doing a sequential scan. Module F-6 (constraints) and the indexing module both come back to this.

---

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 →

---

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:

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:

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.