🎓 Velnov

🗄️ SQL · Topic 13 of 20

Advanced Joins — SQL Questions with Answers

30 practice questions on Advanced Joins, each with the correct answer and a clear explanation of why it is right. Read through them as revision notes, or take this topic as an interactive quiz to test yourself.

1 easy · 14 medium · 15 hard · Part of SQL: Databases from basics to window functions - 20 topics.

Key concepts in this topic

Self joinHierarchiesAliasesPairsCROSS JOINCartesian productCombinationsBugsRecursive CTEMulti-table joinsFan-outLAGLEADNon-equi joinRange joinDuplicatesPerformanceOptimiserOrg chartLEFT JOINWHEREDate spinegenerate_seriesLATERAL

Jump to: Easy · Medium · Hard

Easy questions (1)

Q4. easy

What does CROSS JOIN produce?

SELECT s.size, c.color FROM sizes s CROSS JOIN colors c;
  1. A The Cartesian product - every row of A combined with every row of B
  2. B Only matching rows
  3. C All left rows
  4. D Nothing

Answer: A — The Cartesian product - every row of A combined with every row of B

CROSS JOIN has no ON condition. 5 rows crossed with 4 rows gives 20. Useful for generating combinations like every size/colour pairing.

CROSS JOINCartesian product

Medium questions (14)

Q1. medium

What is a SELF JOIN?

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;
  1. A Joining a table to ITSELF using two aliases
  2. B A join that repeats automatically
  3. C A join with no condition
  4. D A join between two databases

Answer: A — Joining a table to ITSELF using two aliases

The same table appears twice under different aliases, letting you relate rows within one table - such as employees to their managers.

Self joinHierarchies

Q2. medium

Why are aliases mandatory in a self join?

  1. A Both sides are the same table, so aliases distinguish the two roles and remove ambiguity
  2. B For speed
  3. C To sort the rows
  4. D They are optional

Answer: A — Both sides are the same table, so aliases distinguish the two roles and remove ambiguity

Without aliases like e and m, the database cannot tell which 'name' or 'id' you mean. Aliases give each copy of the table a distinct identity.

Self joinAliases

Q5. medium

When is a CROSS JOIN genuinely useful?

  1. A Generating all combinations - e.g. a calendar of every date x every store, or product variants
  2. B It is never useful
  3. C For filtering rows
  4. D For sorting

Answer: A — Generating all combinations - e.g. a calendar of every date x every store, or product variants

Deliberate CROSS JOINs build complete grids: all days for all stores to plot sales (filling gaps with zeros), or all size/colour combinations for inventory.

CROSS JOINCombinations

Q8. medium

How do you join four tables in one query?

FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id
  1. A Chain the JOINs, each with its own ON condition following the relationships
  2. B Use four FROM clauses
  3. C You cannot join four tables
  4. D Use four subqueries

Answer: A — Chain the JOINs, each with its own ON condition following the relationships

Multi-table joins are routine in reporting. Follow the foreign keys: orders to customers, orders to items, items to products.

Multi-table joins

Q10. medium

How do you compare each row to the previous row using a self join?

  1. A Join the table to itself ON b.id = a.id - 1 (or on a matching sequence)
  2. B It cannot be done
  3. C Use CROSS JOIN
  4. D Use GROUP BY

Answer: A — Join the table to itself ON b.id = a.id - 1 (or on a matching sequence)

Self joining on adjacent ids compares consecutive rows - though window functions (LAG/LEAD, topic 20) are the modern, more reliable tool for this.

Self joinLAGLEAD

Q13. medium

How do you find duplicate rows using a self join?

  1. A Join ON matching columns AND a.id <> b.id to find rows that share values
  2. B Use CROSS JOIN
  3. C Duplicates cannot be found
  4. D Use DISTINCT only

Answer: A — Join ON matching columns AND a.id <> b.id to find rows that share values

Rows with equal email but different id are duplicates. GROUP BY ... HAVING COUNT(*) > 1 is usually cleaner, but a self join can also list the offending pairs.

DuplicatesSelf join

Q15. medium

How does a self join model an org chart at one level?

  1. A Employee row joins to another employee row via manager_id = id
  2. B It joins two tables
  3. C It uses CROSS JOIN
  4. D It cannot

Answer: A — Employee row joins to another employee row via manager_id = id

Each employee stores their manager's id, which points back into the same table. One self join gives employee + manager; two levels need two joins or recursion.

Self joinOrg chart

Q17. medium

Can CROSS JOIN be combined with a WHERE clause?

  1. A Yes - and a CROSS JOIN plus a WHERE equality condition is effectively an INNER JOIN
  2. B No, CROSS JOIN forbids WHERE
  3. C Only with ON
  4. D It errors

Answer: A — Yes - and a CROSS JOIN plus a WHERE equality condition is effectively an INNER JOIN

FROM a CROSS JOIN b WHERE a.id = b.a_id filters the product down to matches - the old implicit-join style. Explicit INNER JOIN ON expresses the same intent more safely.

CROSS JOINWHERE

Q20. medium

How do you list every combination of two small lists?

  1. A CROSS JOIN the two tables
  2. B INNER JOIN with no ON
  3. C UNION them
  4. D GROUP BY both

Answer: A — CROSS JOIN the two tables

CROSS JOIN is the intended tool for building all combinations, such as a menu of every base x every topping.

CROSS JOINCombinations

Q22. medium

What does joining a lookup/reference table achieve?

  1. A It replaces stored codes with human-readable labels (e.g. status_id -> status name)
  2. B It speeds up writes
  3. C It deletes codes
  4. D Nothing

Answer: A — It replaces stored codes with human-readable labels (e.g. status_id -> status name)

Normalised designs store small codes and keep the labels in a lookup table. Joining brings back readable text: JOIN statuses s ON o.status_id = s.id.

Lookup tableNormalization

Q24. medium

How do you find employees who have the same manager as 'Ana'?

  1. A Self join on manager_id, matching Ana's manager to others
  2. B CROSS JOIN employees
  3. C GROUP BY manager
  4. D It cannot be done

Answer: A — Self join on manager_id, matching Ana's manager to others

Self join a (the person) to b (Ana) on a.manager_id = b.manager_id where b.name = 'Ana'. Peer-finding is a natural self-join task.

Self joinPeers

Q26. medium

What is a bridge (junction) table's role in joins?

  1. A It sits between two tables in a many-to-many relationship, joined to each side
  2. B It stores backups
  3. C It sorts data
  4. D It has no role

Answer: A — It sits between two tables in a many-to-many relationship, joined to each side

students <-> enrollments <-> courses: two joins through the bridge connect the many-to-many sides. The bridge often carries relationship data like a grade.

Junction tableMany-to-many

Q28. medium

How do you show a manager and ALL their direct reports?

  1. A Self join employees (report) to employees (manager) ON report.manager_id = manager.id
  2. B CROSS JOIN employees
  3. C GROUP BY manager only
  4. D It cannot be done

Answer: A — Self join employees (report) to employees (manager) ON report.manager_id = manager.id

One self join lists each direct report alongside their manager. Ordering by manager groups each team together.

Self joinReporting lines

Q30. medium

Which join type has no ON condition by design?

  1. A CROSS JOIN
  2. B INNER JOIN
  3. C LEFT JOIN
  4. D FULL JOIN

Answer: A — CROSS JOIN

CROSS JOIN deliberately pairs every combination and needs no matching condition. All other join types require an ON (or USING) clause.

CROSS JOINJoin types

Hard questions (15)

Q3. hard

How do you find pairs of employees in the SAME department (without duplicates)?

  1. A Self join ON a.dept = b.dept AND a.id < b.id
  2. B Self join ON a.dept = b.dept
  3. C INNER JOIN with GROUP BY
  4. D CROSS JOIN

Answer: A — Self join ON a.dept = b.dept AND a.id < b.id

The a.id < b.id condition prevents matching a row with itself and avoids listing each pair twice (A-B and B-A). A classic self-join interview question.

Self joinPairs

Q6. hard

What is an accidental Cartesian product, and how does it happen?

  1. A A huge result from a missing or wrong join condition - rows multiply instead of matching
  2. B A deliberate CROSS JOIN
  3. C A type of index
  4. D An empty result

Answer: A — A huge result from a missing or wrong join condition - rows multiply instead of matching

Forgetting ON (or a comma-join without WHERE) pairs every row with every row. Two 10,000-row tables produce 100 million rows - a common cause of runaway queries.

Cartesian productBugs

Q7. hard

How do you build a hierarchy of unlimited depth (e.g. org chart)?

  1. A A recursive CTE (WITH RECURSIVE) - a self join only reaches a fixed number of levels
  2. B Nested self joins forever
  3. C CROSS JOIN
  4. D It is impossible

Answer: A — A recursive CTE (WITH RECURSIVE) - a self join only reaches a fixed number of levels

Each self join adds exactly one level. For arbitrary depth (manager of manager of ...), a recursive CTE walks the tree. (CTEs are covered in topic 15.)

Recursive CTEHierarchies

Q9. hard

In a multi-table join, why can row counts balloon unexpectedly?

  1. A Each one-to-many join multiplies rows - joining orders to items AND to payments can multiply items by payments
  2. B Joins always reduce rows
  3. C It is a syntax error
  4. D Only with CROSS JOIN

Answer: A — Each one-to-many join multiplies rows - joining orders to items AND to payments can multiply items by payments

If an order has 3 items and 2 payments, joining both to the order yields 3 x 2 = 6 rows. Aggregate each branch separately (in subqueries) before combining to avoid inflated totals.

  • A hard-to-spot cause of wrong sums in reports.

Fan-outMulti-table joins

Q11. hard

What does a self join reveal here? SELECT a.name FROM employees a JOIN employees b ON a.salary < b.salary WHERE b.name = 'Ana';

  1. A Employees who earn LESS than Ana
  2. B Ana's manager
  3. C Employees earning more than Ana
  4. D An error

Answer: A — Employees who earn LESS than Ana

Self joins with inequality conditions compare rows against a reference row - here, everyone paid below Ana. Non-equi joins (using <, >, BETWEEN) are a powerful advanced technique.

Non-equi joinSelf join

Q12. hard

What is a non-equi join?

SELECT s.id, t.discount
FROM sales s JOIN tiers t ON s.amount BETWEEN t.min_amt AND t.max_amt;
  1. A A join whose condition uses something other than equality (<, >, BETWEEN)
  2. B A join with no condition
  3. C An invalid join
  4. D A join on two equal columns

Answer: A — A join whose condition uses something other than equality (<, >, BETWEEN)

For example joining sales to a tier table ON amount BETWEEN tier.low AND tier.high to assign a discount band. Equality is common but not required.

Non-equi joinRange join

Q14. hard

Why order multi-table joins to filter the largest table early?

  1. A Reducing rows early means later joins process fewer rows - though the optimiser often reorders automatically
  2. B Order never matters
  3. C It changes the result
  4. D To avoid syntax errors

Answer: A — Reducing rows early means later joins process fewer rows - though the optimiser often reorders automatically

For INNER joins the RESULT is order-independent, but applying selective filters early can help performance. Good indexes and current statistics matter more than manual ordering.

PerformanceOptimiser

Q16. hard

What does a LEFT self join achieve for the top of a hierarchy?

  1. A It keeps the CEO (whose manager_id is NULL) in the result with a NULL manager
  2. B It removes the CEO
  3. C It duplicates the CEO
  4. D Nothing

Answer: A — It keeps the CEO (whose manager_id is NULL) in the result with a NULL manager

An INNER self join would drop the top person because they have no manager to match. LEFT JOIN preserves them, showing NULL for the manager column.

LEFT JOINSelf join

Q18. hard

How do you generate a row for every date in a range (a date spine)?

-- PostgreSQL
SELECT d::date, COALESCE(SUM(s.amt),0)
FROM generate_series('2026-01-01','2026-01-31',INTERVAL '1 day') d
LEFT JOIN sales s ON s.day = d::date GROUP BY 1;
  1. A CROSS JOIN or a recursive CTE / generate_series to produce all dates, then LEFT JOIN the data
  2. B Data always has every date
  3. C GROUP BY date
  4. D It is impossible

Answer: A — CROSS JOIN or a recursive CTE / generate_series to produce all dates, then LEFT JOIN the data

Reports often need zero-filled gaps (days with no sales). Build a complete date spine, then LEFT JOIN the actual data so missing days show 0.

Date spinegenerate_series

Q19. hard

What does LATERAL / CROSS APPLY allow?

SELECT c.name, o.total
FROM customers c
CROSS JOIN LATERAL (
  SELECT total FROM orders WHERE customer_id = c.id ORDER BY total DESC LIMIT 3
) o;
  1. A A subquery in the FROM clause that references columns from the preceding table - like a per-row lookup
  2. B Nothing new
  3. C A faster CROSS JOIN
  4. D Joining three tables

Answer: A — A subquery in the FROM clause that references columns from the preceding table - like a per-row lookup

LATERAL (PostgreSQL) and CROSS APPLY (SQL Server) let a join's right side depend on the left row - perfect for 'top 3 orders per customer'.

LATERALCROSS APPLY

Q21. hard

In a query joining orders, items and payments, how do you avoid inflated sums?

  1. A Aggregate items and payments in SEPARATE subqueries first, then join the summaries to orders
  2. B Use DISTINCT on everything
  3. C Join all three directly and SUM
  4. D Use CROSS JOIN

Answer: A — Aggregate items and payments in SEPARATE subqueries first, then join the summaries to orders

Joining two one-to-many branches multiplies rows. Pre-aggregating each branch to one row per order removes the fan-out before you combine them.

Fan-outSubqueriesAggregation

Q23. hard

What is the danger of SELECT * in a multi-table join?

  1. A Duplicate column names, huge unnecessary output, and fragile code if any table changes
  2. B It is always fine
  3. C It errors
  4. D It sorts the result

Answer: A — Duplicate column names, huge unnecessary output, and fragile code if any table changes

Several tables may each have an 'id' or 'name'. Selecting only the qualified columns you need avoids ambiguous output and reduces data transfer.

SELECT *Best practices

Q25. hard

How does an anti-join differ from a semi-join?

  1. A Semi-join keeps left rows that HAVE a match (EXISTS); anti-join keeps those that have NO match (NOT EXISTS)
  2. B They are the same
  3. C Semi-join duplicates rows
  4. D Anti-join is invalid

Answer: A — Semi-join keeps left rows that HAVE a match (EXISTS); anti-join keeps those that have NO match (NOT EXISTS)

EXISTS is a semi-join (existence test, no fan-out). NOT EXISTS or LEFT JOIN ... IS NULL is an anti-join. Both return left columns only, unlike a normal join.

Semi-joinAnti-joinEXISTS

Q27. hard

Why can a range self join (t.value BETWEEN a AND b) be slow?

  1. A Range conditions are harder to index than equality, often forcing more comparisons
  2. B It is always fast
  3. C Ranges are not allowed in joins
  4. D It errors

Answer: A — Range conditions are harder to index than equality, often forcing more comparisons

Equality joins can use hash/merge strategies efficiently; range joins often fall back to nested loops. Appropriate indexes and small tier tables keep them manageable.

Non-equi joinPerformance

Q29. hard

What is the safest way to verify a complex join is correct?

  1. A Check the row count and spot-check sample rows against expected relationships before trusting aggregates
  2. B Assume it is correct
  3. C Only run it in production
  4. D Add more joins

Answer: A — Check the row count and spot-check sample rows against expected relationships before trusting aggregates

Unexpected row counts reveal fan-out or Cartesian products. Verifying the grain (one row per what?) before aggregating prevents silently wrong reports.

TestingDebugging joins

Practice “Advanced Joins” as a quiz