1:N is the most common relationship. One row in table A corresponds to multiple rows in table B, so a JOIN expands each row on the “1” side by the number of matching rows on the “N” side.

-- 1:N example: 1 department → N employees
SELECT d.dept_name, e.name
FROM departments AS d
INNER JOIN employees AS e
  ON d.dept_id = e.dept_id;
-- departments: 3 rows → after JOIN: 5 rows (expanded by employee count)

INNER JOIN the departments and employees tables, and retrieve each employee's name (emp_name) and department name (dept_name).

Note: Human Resources (dept_id=3) has no employees, so INNER JOIN excludes it.

SELECT
  e.name AS emp_name,
  d.dept_name
FROM departments AS d
INNER JOIN employees AS e
  ON d.dept_id = e.dept_id  -- 1 department → N employees (1:N expands rows)
ORDER BY d.dept_id, e.emp_id;

/*
  Execution order:
  1. FROM departments AS d      → Read rows
  2. INNER JOIN employees AS e  → Join (matching rows only)
  3. SELECT e.name, d.dept_name → Evaluate columns
  4. ORDER BY                   → Sort and output
*/

This is the pattern for aggregating the row expansion caused by the 1:N JOIN from Q2. Group the N expanded rows by the original key on the “1” side, then aggregate them with COUNT or SUM.

-- Row-count flow: expand → aggregate
customers(3 rows)
  → INNER JOIN orders → expand to 6 rows (1:N)
  → GROUP BY customer_id → aggregate to 3 rows

INNER JOIN customers and orders, and retrieve each customer's order count (order_count) and total amount (total_amount). Return the result in descending order of order count.

SELECT
  c.name,
  COUNT(o.order_id)  AS order_count,
  SUM(o.amount)      AS total_amount
FROM customers AS c
INNER JOIN orders AS o
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name  -- Aggregate expanded rows by the original customer
ORDER BY order_count DESC;

/*
  Execution order:
  1. FROM customers AS c     → Read rows
  2. INNER JOIN orders AS o  → Join (matching rows only)
  3. GROUP BY c.customer_id  → Create groups
  4. Aggregate functions     → Evaluate COUNT and SUM
  5. SELECT                  → Evaluate columns
  6. ORDER BY order_count    → Sort and output
*/

N:N (many-to-many) means that one student attends multiple courses, while one course has multiple students. In an RDBMS, this relationship is represented with a junction table.

-- N:N structure: students ←(1:N)→ enrollments ←(N:1)→ courses
FROM students AS s
INNER JOIN enrollments AS e ON s.student_id = e.student_id
INNER JOIN courses AS c    ON e.course_id = c.course_id;

Join students, enrollments (the junction table), and courses with two INNER JOINs, and list the course names each student is enrolled in.

SELECT
  s.name AS student_name,
  c.course_name
FROM students AS s
INNER JOIN enrollments AS e   -- First: students →(1:N)→ enrollments
  ON s.student_id = e.student_id
INNER JOIN courses AS c  -- Second: enrollments →(N:1)→ courses
  ON e.course_id = c.course_id
ORDER BY s.student_id, c.course_id;

/*
  Execution order:
  1. FROM students AS s            → Read students
  2. INNER JOIN enrollments AS e   → Join (expand through 1:N)
  3. INNER JOIN courses AS c       → Join (N:1)
  4. SELECT s.name, c.course_name  → Project two columns
  5. ORDER BY                      → Sort by student_id and course_id
  */

A fan trap occurs when two 1:N tables are JOINed to the same table at the same time, causing unintended row multiplication (a Cartesian product within each user). It is one of the most common JOIN bugs and brings together the cardinality concepts from this set.

-- [Warning] Wrong: JOIN two 1:N tables at once → Cartesian product
FROM users AS u
INNER JOIN orders AS o  ON u.user_id = o.user_id  -- 1:N (1)
INNER JOIN reviews AS r ON u.user_id = r.user_id  -- 1:N (2)
-- Tanaka: 2 orders × 2 reviews = 4 rows — inflated!

Retrieve each user's total order amount (total_amount) and average review rating (avg_rating). To avoid the fan trap, solve it with the correct approach: aggregate each 1:N table separately, then JOIN the aggregates.

Note: with the incorrect query, Tanaka's total_amount becomes 9000 instead of 4500 (it doubles because of the Cartesian product).

-- ✓ Correct approach: aggregate each 1:N separately → safely JOIN as 1:1
SELECT
  u.name,
  o_agg.total_amount,
  r_agg.avg_rating
FROM users AS u
INNER JOIN (
  SELECT user_id, SUM(amount) AS total_amount
  FROM orders
  GROUP BY user_id
) AS o_agg ON u.user_id = o_agg.user_id
INNER JOIN (
  SELECT user_id, ROUND(AVG(rating), 1) AS avg_rating
  FROM reviews
  GROUP BY user_id
) AS r_agg ON u.user_id = r_agg.user_id;

/*
  Execution order:
  1. Subquery 1            → Aggregate orders (o_agg)
  2. Subquery 2            → Aggregate reviews (r_agg)
  3. FROM users AS u        → Read users
  4. INNER JOIN o_agg      → Join on user_id
  5. INNER JOIN r_agg      → Join on user_id
  6. SELECT                → Output the result
  */