intermediateJOINs — INNER, LEFT, RIGHT, FULL, SELF, CROSS

When would you use a SELF JOIN vs a CTE?

SELF JOIN: use for comparing rows within the same table (finding duplicates, adjacent rows, hierarchies). Example: employees with their managers. Simple, single-level comparisons. CTE with recursive: use for arbitrary-depth hierarchies (org charts, category trees, bill of materials). SELF JOIN can only do 1 level at a time; recursive CTE can traverse unlimited depth. Example: finding all subordina

Ready to master this question?

Generate a complete walkthrough — background, the full answer in plain language, a working code example explained line by line, a real-world scenario, common mistakes, and how this same question gets asked in different ways.

Next Step

Continue to You need a report showing all employees and their total order amounts this year. Employees with no orders should show 0. How do you write this? →← Back to all SQL questions