What is a self join, and what kinds of question is it the only way to answer?
A self join is a join where a table appears twice, with two different names.
So what kinds of question is it the only way to answer? Three kinds.
The first one is a question about a relationship inside the table. Who is the manager of this employee? Which category is the parent of this one? Which invoice is the earlier of this pair? The table refers to itself, so you need two copies of it to line up the two sides.
The second one is a question about two rows of the same table. Which customers live in the same city? Which products cost more than another product of the same kind? That is a comparison between two rows, and you need the table twice for it.
The third one is a question about duplicates. Which email addresses appear more than once? Which customers have the same name? Here you line up the table with itself on the value you are comparing.
-- The three shapes, in one picture.
--
-- A relationship inside the table:
-- staff e JOIN staff m ON e.manager_id = m.id
--
-- Two rows compared with each other:
-- customers a JOIN customers b ON a.city = b.city AND a.id < b.id
--
-- Duplicates:
-- customers a JOIN customers b ON a.email = b.email AND a.id < b.id
There is one rule that makes a self join work, and it is easy to forget. The two copies have to have different names, and you give those names with the keyword AS after the table name. The alias is not decoration, it is what tells the engine which copy you mean.
The next question in this topic asks for the first of the three shapes.