INNER JOIN

Learning Focus

Use this lesson to understand INNER JOIN with practical syntax and examples.

Concept Overview

An INNER JOIN combines rows from two tables when the join condition matches on both sides. If there is no match, the row is excluded from the result.

In plain language, an inner join answers:

"Show me only rows where both tables have related data."

Why is it important?

• Normalized schemas: related data is split across tables (customers vs orders), and joins rebuild the full view
• Correctness: most reporting queries require combining facts (orders) with dimensions (customers, products)
• Performance: the way you join (keys, filters, cardinality) determines whether a query is fast and accurate

Where does it fit?

INNER JOIN is the default join type you use when you only want matched rows. It is foundational for later join types (LEFT JOIN, self joins) and for aggregation across tables.

---

Syntax & Rules

Core Syntax

SELECT select_list
FROM table_a a
INNER JOIN table_b b
  ON b.a_id = a.id;

Available Options / Parameters

Part	What it does	Notes
INNER JOIN	keeps only matched rows	equivalent to JOIN
ON	defines match condition	most commonly PK/FK equality
Table aliases	shorten and clarify column references	recommended for joins
Additional predicates	WHERE filters result after join	join condition vs filter is important

Key Rules and Considerations

• Rows with no match are excluded.
• NULL values in join keys typically do not match (because NULL = NULL is unknown).
• If the join condition is not one-to-one (or many-to-one), results can multiply rows.
• Qualify columns (c.customer_id) to avoid ambiguity and to make intent clear.

---

Step-by-Step Examples

Example 1: Basic INNER JOIN (Beginner)

CREATE TABLE customers (
  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   text NOT NULL
);

CREATE TABLE orders (
  order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(customer_id),
  amount      numeric(12,2) NOT NULL CHECK (amount >= 0)
);

INSERT INTO customers (full_name) VALUES
  ('Alice'),
  ('Bob');

INSERT INTO orders (customer_id, amount) VALUES
  (1, 120.00),
  (1, 80.00);

SELECT c.customer_id, c.full_name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Expected output:

 customer_id | full_name | order_id | amount
-------------+-----------+----------+--------
 1           | Alice     | 1        | 120.00
 1           | Alice     | 2        | 80.00
(2 rows)

Explanation:

• Bob is excluded because he has no matching row in orders.

---

Example 2: Join + Filter + Aliases (Intermediate)

-- Only orders >= 100
SELECT c.full_name AS customer, o.order_id, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount >= 100
ORDER BY o.order_id;

Expected output:

 customer | order_id | amount
----------+----------+--------
 Alice    | 1        | 120.00
(1 row)

Explanation:

• JOIN without INNER is still an inner join.
• WHERE filters the joined rows after matching.

---

Example 3: Row Multiplication (Cardinality) (Advanced)

Inner joins can multiply rows if you join a “one” table to a “many” table (or many-to-many). This is correct behavior, but it can surprise you in metrics.

CREATE TABLE order_items (
  item_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id bigint NOT NULL REFERENCES orders(order_id),
  sku      text NOT NULL,
  qty      integer NOT NULL CHECK (qty > 0)
);

INSERT INTO order_items (order_id, sku, qty) VALUES
  (1, 'MOUSE', 1),
  (1, 'PAD', 2),
  (2, 'KEY', 1);

-- Joining orders to items multiplies order rows
SELECT o.order_id, o.amount, i.sku, i.qty
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
ORDER BY o.order_id, i.item_id;

Expected output:

 order_id | amount | sku   | qty
----------+--------+-------+-----
 1        | 120.00 | MOUSE | 1
 1        | 120.00 | PAD   | 2
 2        | 80.00  | KEY   | 1
(3 rows)

If you try to sum order amounts after this join, you will overcount order 1 (it appears twice).

Fix: aggregate at the correct grain.

-- Correct: sum amounts from orders only
SELECT SUM(amount) AS total_amount
FROM orders;

Expected output:

 total_amount
------------
 200.00
(1 row)

Or, if you must join, aggregate first:

WITH per_order AS (
  SELECT order_id, amount
  FROM orders
)
SELECT SUM(amount) AS total_amount
FROM per_order;

---

Practical Use Cases

1) Customer orders report

SELECT c.customer_id, c.full_name, o.order_id, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

2) Products and suppliers

SELECT p.product_id, p.name, s.supplier_name
FROM products p
JOIN suppliers s ON s.supplier_id = p.supplier_id;

3) Enrollment systems

SELECT s.student_id, s.full_name, c.course_code
FROM students s
JOIN enrollments e ON e.student_id = s.student_id
JOIN courses c ON c.course_id = e.course_id;

4) Operational audit: find invoices with payments

SELECT i.invoice_id, p.payment_id, p.captured_at
FROM invoices i
JOIN payments p ON p.invoice_id = i.invoice_id;

5) Analytics: join dimensions to facts

SELECT o.order_id, o.amount, c.full_name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

---

Common Mistakes & Troubleshooting

1) Joining on the wrong columns

Wrong SQL:

-- Wrong: unrelated keys
SELECT *
FROM customers c
JOIN orders o ON o.order_id = c.customer_id;

Bad outcome:

• Returns wrong matches (or none), often without an error.

Fix:

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

---

2) Forgetting to qualify ambiguous columns

Wrong SQL:

SELECT customer_id
FROM customers
JOIN orders ON orders.customer_id = customers.customer_id;

Typical error:

ERROR:  column reference "customer_id" is ambiguous

Fix:

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

---

3) Accidentally turning a filter into a join restriction

Wrong approach:

-- You might expect customers with no orders to appear, but INNER JOIN never includes them.
SELECT c.customer_id, o.order_id
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

Bad outcome:

• Customers without orders are excluded.

Fix:

• Use LEFT JOIN when you need unmatched left-side rows.

---

4) Overcounting after joins

Wrong SQL:

SELECT SUM(o.amount)
FROM orders o
JOIN order_items i ON i.order_id = o.order_id;

Bad outcome:

• o.amount is repeated once per item, inflating totals.

Fix:

SELECT SUM(amount) FROM orders;

---

Debugging checklist:

1. Verify the join keys: identify the PK/FK pair.
2. Run each table query separately to confirm expected rows.
3. Start with a small SELECT ... LIMIT 10 and inspect matched pairs.
4. Check cardinality: does one row match many rows (multiplication)?
5. Use EXPLAIN if the join is slow and ensure join keys are indexed.

---

Best Practices

• ✅ Join on primary/foreign keys (or unique keys). ❌ Avoid joining on “name” columns or non-unique fields.
• ✅ Always qualify columns in join queries. ❌ Avoid ambiguous column names in multi-table queries.
• ✅ Add deterministic tie-breakers when ordering joined results. ❌ Avoid relying on accidental row order.
• ✅ Watch join grain when computing metrics (avoid double counting). ❌ Avoid summing fact columns after a join that multiplies rows.
• ✅ Index join keys on large tables. ❌ Avoid large joins without supporting indexes.

---

Hands-On Practice

Use this setup for the exercises:

CREATE TABLE practice_customers (
  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   text NOT NULL
);

CREATE TABLE practice_orders (
  order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint REFERENCES practice_customers(customer_id),
  amount      numeric(12,2) NOT NULL
);

INSERT INTO practice_customers (full_name) VALUES
  ('Alice'),
  ('Bob'),
  ('Carol');

INSERT INTO practice_orders (customer_id, amount) VALUES
  (1, 10.00),
  (1, 25.00),
  (3, 5.00);

Exercise 1 (Easy): Join customers to orders

Task: Return customer name and order amount for all orders.

-- Your SQL here

Solution:

SELECT c.full_name, o.amount
FROM practice_customers c
JOIN practice_orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

---

Exercise 2 (Medium): Filter joined rows

Task: Return orders >= 20 with the customer name.

-- Your SQL here

Solution:

SELECT c.full_name, o.order_id, o.amount
FROM practice_customers c
JOIN practice_orders o ON o.customer_id = c.customer_id
WHERE o.amount >= 20
ORDER BY o.order_id;

---

Exercise 3 (Advanced): Identify excluded rows

Task: Demonstrate that Bob (no orders) is excluded by INNER JOIN by writing a query that returns only customers that have orders.

-- Your SQL here

Solution:

SELECT c.customer_id, c.full_name
FROM practice_customers c
JOIN practice_orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.full_name
ORDER BY c.customer_id;

---

Connection to Other Concepts

Concept	Why it matters
Primary/foreign keys	define correct join relationships
LEFT JOIN	includes unmatched left-side rows
GROUP BY + aggregates	common to join then aggregate (careful with grain)
Aliases (AS)	make join queries readable and avoid ambiguity
UNION ALL	combines result sets (rows), not related tables

---

Visual Learning Diagram

flowchart TD
  A[Normalized Tables] --> B[Join Keys (PK/FK)]
  B --> C[INNER JOIN]
  C --> D[Matched Rows Only]
  C --> E[Cardinality Awareness]
  E --> F[Avoid Double Counting]
  C --> G[LEFT JOIN]
  C --> H[GROUP BY + JOIN]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G,H allNodes
  class C highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Wrong join keys	incorrect results	join on PK/FK or unique keys
Unqualified columns	ambiguous errors	use table aliases and qualify
Many-to-many joins without awareness	row explosion	pre-aggregate or constrain joins
Aggregating after row-multiplying joins	inflated metrics	aggregate at the correct grain
Using INNER JOIN when you need unmatched rows	missing data	switch to LEFT JOIN

---

Quick Reference

SELECT a.*, b.* FROM a JOIN b ON b.a_id = a.id;
SELECT c.name, o.amount FROM customers c INNER JOIN orders o ON o.customer_id = c.customer_id;
SELECT DISTINCT ON (key) * FROM t ORDER BY key, created_at DESC;
SELECT * FROM a JOIN b ON ... WHERE ...;
SELECT ... FROM a JOIN b ON ... GROUP BY ...;

What's Next

• Next: LEFT JOIN - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.