SQL Quiz: Self Joins
10 questions · exam conditions
0:00
Self JoinsQuestion 1 of 10

The PriceHistory table has columns product_id, effective_date, and price. Each product has at most one row for a given effective_date. A query must return the most recent price row for every product without using a subquery or window function.

Which query uses a self-join to return the required rows?

SELECT p1.* FROM PriceHistory p1 JOIN PriceHistory p2 ON p1.product_id = p2.product_id WHERE p2.effective_date > p1.effective_date;
SELECT p1.* FROM PriceHistory p1 LEFT JOIN PriceHistory p2 ON p1.product_id = p2.product_id AND p2.effective_date > p1.effective_date WHERE p2.product_id IS NULL;
SELECT p1.* FROM PriceHistory p1 LEFT JOIN PriceHistory p2 ON p1.product_id = p2.product_id WHERE p2.effective_date > p1.effective_date OR p2.product_id IS NULL;
SELECT p1.* FROM PriceHistory p1 LEFT JOIN PriceHistory p2 ON p1.product_id = p2.product_id AND p2.effective_date < p1.effective_date WHERE p2.product_id IS NULL;
← Back to quizzes

SQL Quiz

SQL Quiz: Self Joins

Practice Self Joins in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Self Joins, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

The PriceHistory table has columns product_id, effective_date, and price. Each product has at most one row for a given effective_date. A query must return the most recent price row for every product without using a subquery or window function.

Which query uses a self-join to return the required rows?

  1. SELECT p1.* FROM PriceHistory p1 JOIN PriceHistory p2 ON p1.product_id = p2.product_id WHERE p2.effective_date > p1.effective_date;
  2. SELECT p1.* FROM PriceHistory p1 LEFT JOIN PriceHistory p2 ON p1.product_id = p2.product_id AND p2.effective_date > p1.effective_date WHERE p2.product_id IS NULL; (correct answer)
  3. SELECT p1.* FROM PriceHistory p1 LEFT JOIN PriceHistory p2 ON p1.product_id = p2.product_id WHERE p2.effective_date > p1.effective_date OR p2.product_id IS NULL;
  4. SELECT p1.* FROM PriceHistory p1 LEFT JOIN PriceHistory p2 ON p1.product_id = p2.product_id AND p2.effective_date < p1.effective_date WHERE p2.product_id IS NULL;
Explanation: When you need the "most recent" row per group without subqueries or window functions, a classic self-join technique works by asking: "Does any newer row exist for this product?" If no newer row exists, you're looking at the most recent one. The trick in option B is the LEFT JOIN combined with a NULL check. By joining p1 to p2 on the same product_id and requiring p2.effective_date > p1.effective_date, you're trying to find a row that is newer than p1. When the LEFT JOIN finds no such match — meaning p2.product_id IS NULL — it proves that nothing is newer than p1, so p1 must be the most recent row. That's exactly the pattern B uses, and it correctly returns one row per product. Option A uses an INNER JOIN and filters WHERE p2.effective_date > p1.effective_date. This keeps only rows where a newer row exists — the exact opposite of what you want. It eliminates the most recent rows entirely. Option C moves the condition p2.effective_date > p1.effective_date into the WHERE clause instead of the JOIN condition, then ORs it with p2.product_id IS NULL. This creates a logical mess: the OR means you'd include rows where a newer record exists OR where there's no match at all, returning incorrect results. Option D joins on p2.effective_date < p1.effective_date (looking for older rows) and then checks for NULL, which would return only products with a single row — not the most recent row for every product. As a study tip, remember: in a self-join "greatest-per-group" pattern, the join condition finds rows that beat the current row, and the IS NULL filter confirms no such row exists — making the current row the winner.

Question 2

The Employees table contains employee_id, employee_name, and manager_id, where manager_id references another employee. A report should include only employees who have both an immediate manager and a grandmanager, displaying all three names.

Which FROM clause correctly traverses the hierarchy by two levels?

  1. FROM Employees e JOIN Employees m ON e.manager_id = m.employee_id JOIN Employees g ON e.manager_id = g.employee_id
  2. FROM Employees e JOIN Employees m ON e.manager_id = m.employee_id JOIN Employees g ON g.manager_id = m.employee_id
  3. FROM Employees e JOIN Employees m ON e.manager_id = m.employee_id JOIN Employees g ON m.manager_id = e.employee_id
  4. FROM Employees e JOIN Employees m ON e.manager_id = m.employee_id JOIN Employees g ON m.manager_id = g.employee_id (correct answer)
Explanation: When working with self-referencing (hierarchical) tables in SQL, the key is to ask: which column connects which alias to which? Each JOIN must chain one level higher in the hierarchy — employee → manager → grandmanager — so the relationship between aliases must follow that sequence precisely. To traverse two levels, you need three instances of the Employees table: e (employee), m (manager), and g (grandmanager). The first JOIN correctly links e.manager_id = m.employee_id, establishing that m is e's direct manager. The second JOIN must then climb one more level by finding m's own manager — meaning you need m.manager_id = g.employee_id. That's exactly what D does, making g the grandmanager. The chain reads: e → m → g, each hop moving one level up. A is wrong because both JOINs use e.manager_id, which links both m and g to the same person — e's direct manager. You never climb to the second level at all. B reverses the direction of the second JOIN by writing g.manager_id = m.employee_id. This actually makes m the manager of g, meaning g would be lower in the hierarchy than m, not higher — you'd be going sideways or downward, not upward. C is logically broken: m.manager_id = e.employee_id would make the grandmanager column point back to the employee themselves, creating a nonsensical circular reference. Strategy tip: When writing self-joins for hierarchies, sketch the chain on paper first — each arrow should point one level up, and the right side of each ON clause should always be employee_id of the next-higher alias.

Question 3

The Reservations table contains reservation_id, room_id, start_date, and end_date. Both endpoint dates are included in a reservation. A report must identify every unordered pair of different reservations that overlap in the same room, returning each pair once.

Which self-join condition correctly identifies the required pairs?

  1. ON r1.room_id = r2.room_id AND r1.reservation_id < r2.reservation_id AND r1.end_date < r2.start_date
  2. ON r1.room_id = r2.room_id AND r1.reservation_id <> r2.reservation_id AND r1.start_date <= r2.end_date
  3. ON r1.room_id = r2.room_id AND r1.reservation_id < r2.reservation_id AND r1.start_date <= r2.end_date AND r2.start_date <= r1.end_date (correct answer)
  4. ON r1.room_id = r2.room_id AND r1.reservation_id < r2.reservation_id AND r1.start_date < r2.start_date AND r1.end_date < r2.end_date
Explanation: When checking whether two date ranges overlap, the key insight is that two intervals [A_start, A_end] and [B_start, B_end] overlap if and only if Astart≤Bend AND Bstart≤AendA_{start} \leq B_{end} \text{ AND } B_{start} \leq A_{end}. This is the classic interval-overlap test. At the same time, a self-join on Reservations produces every ordered pair, so you need r1.reservation_id < r2.reservation_id to guarantee each unordered pair appears exactly once. Option C combines both requirements perfectly: r1.room_id = r2.room_id ensures you're comparing the same room, r1.reservation_id < r2.reservation_id eliminates duplicates and self-joins, and the two date conditions r1.start_date <= r2.end_date AND r2.start_date <= r1.end_date correctly capture all forms of overlap — whether one reservation is fully inside the other, they partially overlap, or they share a single boundary date. Option A uses r1.end_date < r2.start_date, which actually identifies non-overlapping reservations where r1 ends before r2 begins — the opposite of what's needed. Option B correctly uses <> to avoid self-joins but only checks one direction of the overlap condition, missing cases where r2 starts before r1 ends; it would also return duplicate pairs (r1,r2) and (r2,r1). Option D restricts to cases where r1 strictly starts and ends before r2, missing overlaps where the reservations partially interleave or one contains the other. A reliable study tip: memorize the interval-overlap test as "neither ends before the other begins" — two ranges overlap when Astart≤BendA_{start} \leq B_{end} and Bstart≤AendB_{start} \leq A_{end}. Apply both halves every time.

Question 4

The ProjectAssignments table contains one row per employee-project assignment, with columns employee_id and project_id. A report must list each pair of different employees assigned to the same project. Each unordered pair must appear only once.

Which self-join condition correctly identifies the required employee pairs?

  1. ON a1.project_id = a2.project_id AND a1.employee_id <> a2.employee_id
  2. ON a1.project_id = a2.project_id AND a1.employee_id <= a2.employee_id
  3. ON a1.project_id = a2.project_id AND a1.employee_id < a2.employee_id (correct answer)
  4. ON a1.project_id <> a2.project_id AND a1.employee_id < a2.employee_id
Explanation: When performing a self-join to find pairs of employees sharing a project, you need to think carefully about two things: matching rows on the right column, and avoiding duplicate or invalid pairs. The join must connect rows where both aliases share the same project, so a1.project_id = a2.project_id is always required as the first condition. That narrows it down to A, B, or C immediately — D fails right away because it uses <> on project_id, which would match employees on different projects, the exact opposite of what's needed. Now the challenge is handling duplicates. Suppose employees 3 and 7 share a project. A naive join produces both (3, 7) and (7, 3) — the same pair twice — plus self-matches like (3, 3). You need a condition on employee_id that eliminates both problems. Option A uses <>, which removes self-matches but still returns both (3, 7) and (7, 3). Every unordered pair appears twice, violating the requirement. Option B uses <=, which does reduce duplicates but still allows (3, 3) — a self-match where both sides are equal — since 3 ≤ 3 is true. That means employees can be paired with themselves. Option C uses <, which is the correct answer. Strict less-than eliminates self-matches (a number is never less than itself) and ensures each unordered pair appears exactly once by enforcing a consistent ordering. Only (3, 7) is produced, never (7, 3). A useful tip: whenever you need unique, unordered pairs from a self-join, < on the ID column is your go-to pattern — it does double duty by blocking both self-pairings and reversed duplicates.

Question 5

In the Employees table, manager_id references employee_id. Due to a data-entry problem, two different employees may each identify the other as manager. A report must list every such two-employee cycle exactly once and must not report an employee who incorrectly identifies themself as manager.

Which query correctly finds the cycles?

  1. SELECT e.employee_id, m.employee_id FROM Employees e JOIN Employees m ON e.manager_id = m.employee_id WHERE m.manager_id = e.employee_id;
  2. SELECT e.employee_id, m.employee_id FROM Employees e JOIN Employees m ON e.manager_id = m.manager_id WHERE e.employee_id < m.employee_id;
  3. SELECT e.employee_id, m.employee_id FROM Employees e JOIN Employees m ON e.manager_id = m.employee_id WHERE m.manager_id = e.employee_id AND e.employee_id <> m.employee_id;
  4. SELECT e.employee_id, m.employee_id FROM Employees e JOIN Employees m ON e.manager_id = m.employee_id WHERE m.manager_id = e.employee_id AND e.employee_id < m.employee_id; (correct answer)
Explanation: When detecting mutual-reference cycles in a self-joining table, you need to think about three things simultaneously: correctly matching the cycle relationship, excluding self-references, and avoiding duplicate rows. The core logic for finding a cycle is: employee e lists m as their manager, AND m lists e as their manager. That translates to joining on e.manager_id = m.employee_id (finding who e points to) and then filtering with WHERE m.manager_id = e.employee_id (confirming the relationship is mutual). Option D does exactly this, and the additional condition e.employee_id < m.employee_id is the key deduplication trick — it ensures each pair appears only once (e.g., the pair {101, 202} appears as 101 < 202, never as 202 < 101) and simultaneously eliminates self-references since no number is less than itself. Option A gets the cycle detection right but omits both the self-reference exclusion and the deduplication guard, meaning an employee who incorrectly lists themselves as manager would appear, and every valid cycle would be reported twice. Option B is fundamentally broken in its JOIN — matching on e.manager_id = m.manager_id finds employees who share the same manager, not employees who point to each other. The e.employee_id < m.employee_id condition here is correct in spirit but applied to the wrong problem. Option C correctly detects cycles and excludes self-references with e.employee_id <> m.employee_id, but it still reports each cycle twice (once as (A, B) and once as (B, A)). Study tip: Whenever a self-join can produce reciprocal duplicates, replacing <> with < in your deduplication condition kills two birds with one stone — it removes duplicates AND eliminates self-matches.

Question 6

The Routes table contains one row per directed route, with columns route_id, origin, and destination. There are no duplicate directed routes. A report must list city pairs for which a route exists in both directions, with each city pair shown only once.

Which self-join condition correctly supports the report?

  1. ON r1.origin = r2.destination AND r1.route_id <> r2.route_id AND r1.origin < r1.destination
  2. ON r1.origin = r2.destination AND r1.destination = r2.origin AND r1.origin < r1.destination (correct answer)
  3. ON r1.origin = r2.destination AND r1.destination = r2.origin AND r1.route_id <> r2.route_id
  4. ON r1.origin = r2.origin AND r1.destination = r2.destination AND r1.origin < r1.destination
Explanation: When writing a self-join to find bidirectional routes, you need to think about two separate problems: matching the correct row pairs, and eliminating duplicates from the result. For the match to work, you need r1.origin = r2.destination AND r1.destination = r2.origin. This says: "r1 goes from City A to City B, and r2 goes from City B back to City A" — exactly the bidirectional relationship you want. To show each city pair only once (avoiding both A→B and B→A appearing as separate result rows), you add a tiebreaker like r1.origin < r1.destination, which arbitrarily picks one ordering per pair. That combination is precisely what B provides, making it the correct answer. A is close but flawed. It uses r1.origin = r2.destination without the second condition r1.destination = r2.origin, so it doesn't fully constrain the join to true reverse routes — any route ending at r1's origin would qualify, even if the destinations don't match. The route_id inequality also does unnecessary work here. C has the correct matching logic but uses r1.route_id <> r2.route_id instead of r1.origin < r1.destination to handle duplicates. Since the table has no duplicate directed routes, different route IDs are already guaranteed — this condition removes nothing and still returns each pair twice (once as A→B, once as B→A). D joins on matching origins and destinations, which would only find identical routes — the opposite of what you want. As a study tip: in self-join problems, always separate your thinking into two questions — "Does this condition find the right pairs?" and "Does this condition show each pair only once?"

Question 7

The Employees table contains employee_id, employee_name, manager_id, and active. A report must retain every employee but display manager information only when the referenced manager is active. Employees with no manager or with an inactive manager must still appear with a NULL manager name.

Which query satisfies the requirement?

  1. SELECT e.employee_name, m.employee_name FROM Employees e LEFT JOIN Employees m ON e.manager_id = m.employee_id AND m.active = 1; (correct answer)
  2. SELECT e.employee_name, m.employee_name FROM Employees e LEFT JOIN Employees m ON e.manager_id = m.employee_id WHERE m.active = 1;
  3. SELECT e.employee_name, m.employee_name FROM Employees e LEFT JOIN Employees m ON e.manager_id = m.employee_id AND e.active = 1;
  4. SELECT e.employee_name, m.employee_name FROM Employees e LEFT JOIN Employees m ON e.manager_id = m.employee_id WHERE m.active = 1 OR m.employee_id IS NULL;
Explanation: When working with LEFT JOIN in SQL, the most important concept to internalize is the difference between filtering in the ON clause versus the WHERE clause. This distinction becomes critical when you need to preserve all rows from the left table regardless of whether a match exists. A LEFT JOIN guarantees every row from the left table appears in the result. However, if you add conditions to the WHERE clause, you effectively convert it into an INNER JOIN by discarding rows where the right-side columns are NULL. Conditions placed in the ON clause, by contrast, only control which rows from the right table qualify as a match — unmatched left-side rows still survive with NULL values for all right-side columns. Option A is correct because m.active = 1 sits inside the ON clause. This means the join only matches a manager row when that manager is active. If the manager is inactive or missing, the employee row still appears — just with a NULL manager name, exactly as required. Option B places m.active = 1 in the WHERE clause, which eliminates any employee whose manager is inactive or absent, defeating the entire purpose of the LEFT JOIN. Option C filters on e.active = 1 instead, which silently drops all inactive employees from the result — wrong table, wrong column, wrong behavior. Option D tries to recover dropped rows by adding OR m.employee_id IS NULL, but this still excludes employees with inactive managers (who have a non-null employee_id). The key rule to remember: filter the right-side table in ON, not WHERE, whenever you need the left table fully preserved.

Question 8

A Residents table contains three residents in household 10, two residents in household 20, and one resident in household 30. Each resident has a distinct resident_id. The table is self-joined using r1.household_id = r2.household_id AND r1.resident_id < r2.resident_id.

How many rows does the self-join return?

  1. Three rows, because only the household containing three residents contributes pairs.
  2. Four rows, because each household contributes its distinct unordered resident pairs. (correct answer)
  3. Eight rows, because both orientations of every distinct resident pair are returned.
  4. Ten rows, because each resident can also match the same resident identifier.
Explanation: When you see a self-join with a strict inequality like r1.resident_id < r2.resident_id, your job is to count unordered pairs within each group. The < condition eliminates duplicate pairs (like swapping r1 and r2) and self-matches (a resident paired with themselves), giving you exactly the combinations formula: (n2)=n(n−1)2\binom{n}{2} = \frac{n(n-1)}{2} For each household, apply this formula. Household 10 has 3 residents: (32)=3\binom{3}{2} = 3 pairs. Household 20 has 2 residents: (22)=1\binom{2}{2} = 1 pair. Household 30 has 1 resident: (12)=0\binom{1}{2} = 0 pairs. Summing across all households gives 3+1+0=43 + 1 + 0 = 4 rows total, confirming B is correct. Answer A is wrong because it ignores household 20's contribution. Even a group of two residents produces one valid pair, so you can't discard smaller households. Answer C describes what would happen if you used != or no inequality at all — both orientations (r1→r2 and r2→r1) would appear, doubling the count to 8. The < condition explicitly prevents that. Answer D confuses the condition with <=, which would allow a resident to match itself, adding one self-pair per resident (6 total residents + 4 cross-pairs = 10). The strict < rules that out entirely. A quick study tip: whenever you see a self-join, immediately identify whether the join condition uses <, <=, !=, or = — each produces a fundamentally different row count, and exam questions frequently exploit that distinction.

Question 9

The Employees table contains employee_id, employee_name, and manager_id. For top-level employees, manager_id is NULL. Management wants a report containing every employee, including top-level employees, together with the name of the employee's immediate manager when one exists.

Which query produces the required report?

  1. SELECT e.employee_name, m.employee_name AS manager_name FROM Employees e LEFT JOIN Employees m ON e.manager_id = m.employee_id; (correct answer)
  2. SELECT e.employee_name, m.employee_name AS manager_name FROM Employees e INNER JOIN Employees m ON e.manager_id = m.employee_id;
  3. SELECT e.employee_name, m.employee_name AS manager_name FROM Employees e LEFT JOIN Employees m ON e.employee_id = m.manager_id;
  4. SELECT e.employee_name, m.employee_name AS manager_name FROM Employees e RIGHT JOIN Employees m ON e.manager_id = m.employee_id;
Explanation: When a table references itself — like an Employees table where manager_id points back to another employee_id — you're dealing with a self-join. The key questions to ask are: (1) which join type preserves rows with no match, and (2) is the join condition written in the correct direction? Option A is correct because it uses a LEFT JOIN, which keeps all rows from the left table (e, every employee) even when no matching row exists in the right table (m, the manager). The condition e.manager_id = m.employee_id correctly links each employee's manager reference to the manager's own record. Top-level employees have NULL for manager_id, so they find no match — and LEFT JOIN handles that gracefully by returning NULL for manager_name rather than dropping the row. Option B uses INNER JOIN, which only returns rows where a match exists on both sides. Top-level employees (with manager_id = NULL) would be silently excluded from the results — violating the requirement to include everyone. Option C flips the join condition to e.employee_id = m.manager_id, which actually retrieves each employee's subordinates, not their manager. This is a classic direction trap — always trace which column is the "child" and which is the "parent." Option D uses RIGHT JOIN, which would preserve all rows from the right (manager) side. This risks excluding employees who aren't managers themselves, again breaking the "every employee" requirement. Your study tip: in self-joins, always alias the table twice (e for employee, m for manager) and verify the join condition direction — the foreign key (manager_id) should match the primary key (employee_id) on the manager side.

Question 10

The ProductRevisions table contains revision numbers 1, 2, and 4 for product P, and revision numbers 2 and 3 for product Q. Revision numbers are unique within each product. Consider this query: SELECT c.product_id, c.revision_no, p.revision_no FROM ProductRevisions c JOIN ProductRevisions p ON c.product_id = p.product_id AND c.revision_no = p.revision_no + 1;

How many rows are returned by the query?

  1. Two rows: revision 2 with 1 for P, and revision 3 with 2 for Q. (correct answer)
  2. Three rows: all revisions except the lowest revision recorded for each product.
  3. Four rows: every higher revision is paired with each lower revision of its product.
  4. Five rows: every revision is retained, with NULL for a missing predecessor.
Explanation: When a table joins itself, you need to carefully trace every row pair that satisfies the ON condition before counting results. This query is a self-join on ProductRevisions, aliased as c (current) and p (predecessor), matching rows where they share the same product and c.revision_no = p.revision_no + 1 — meaning c is exactly one revision higher than p. Let's walk through the data. For product P, the revisions are 1, 2, and 4. The only pair where one revision equals another plus one is (2, 1) — revision 4 has no revision 3 to pair with. For product Q, revisions are 2 and 3, giving exactly one pair: (3, 2). That's two total rows, confirming answer A is correct. Answer B is wrong because it assumes every non-minimum revision produces a row. Revision 4 for product P is skipped because there's no revision 3 — the join condition requires a consecutive predecessor, not just any lower revision. Answer C confuses this with a non-equijoin using < or <=, which would pair every higher revision with all lower ones, producing a many-to-many explosion of rows. Answer D describes the behavior of a LEFT JOIN, which would preserve unmatched rows with NULL for missing predecessors — but this query uses an inner JOIN, which silently drops rows with no match. As a study strategy, when you see a self-join question, manually enumerate the matching pairs row by row rather than reasoning abstractly. It only takes a moment and prevents the counting errors that make B, C, and D look tempting.