Self-Joins in SQL
A self-join joins a table to itself, treating two instances of the same table as distinct participants in the join. It follows the same mechanics as any other join; the only requirement is that both instances carry separate aliases so PostgreSQL can distinguish them.
Before this INNER JOIN, Column Aliases and Expression Naming
Builds toward Correlated Subqueries
A self-join joins a table to itself, using two separate aliases to treat it as two distinct copies.
You're looking at an org chart. The employees table has a manager_id column that points to another row in the same table — each employee's row stores the ID of their manager, who is also an employee. To get an employee's name alongside their manager's name, both pieces of data live in the same table. You can't pull from two different tables, because there's only one table. You pull from the same table twice, with different aliases.
The syntax looks exactly like a regular join. The only difference is that FROM and JOIN reference the same table name, and you must give each copy a distinct alias:
SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.ide is the employee copy. m is the manager copy. SQL scans e for each employee row, then looks up the matching row in m where m.id equals that employee's manager_id. The result: employee name next to manager name on the same row.
Because this is an INNER JOIN, employees with no manager_id — typically executives at the top of the hierarchy — are excluded. They have no matching row in the manager copy. To include them, use LEFT JOIN:
SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.idEmployees with no manager get NULL in manager_name. Executives included.
Self-joins work with any table that has rows relating to other rows in the same table. Categories with a parent_id, products that reference a base product, org charts, threaded comments:
The same filtering and ordering rules apply as any other join.
The one thing that trips people up: forgetting that each copy of the table needs a distinct alias.
If you write FROM employees JOIN employees ON ... without aliases, SQL raises an error because it can't tell which copy you're referencing in the SELECT list or ON clause. Pick two short, meaningful aliases — e and m for employee and manager, c and p for child and parent — and use them consistently throughout the query.
9 Self-Joins practice problems
Write a query to return the employee name and manager name for every employee who has a manager on record.
Write a query to return the subcategory name and parent category name for every subcategory that belongs to another category.
Write a query to return each employee's name alongside their manager's name and the manager's job title.
Write a query to return the employee name and manager name for every Engineering team member who has a manager on record.
Write a query to return the subcategory name and parent category name for every subcategory whose parent is `'Clothing'`.
Write a query to return all three values for every employee with a manager on record.
Write a query to return the employee name and manager name for every such employee.
Write a query to return the name of each category in every sibling pair.
Write a query to return the employee's name, their direct manager's name, and their manager's manager's name for every employee who has both levels available.
These problems are part of the Self-Joins lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.