First, I think everyone gets confused by recursive join the first time they see it. It just takes a little time and practice to figure out and accept what is happening here.
When we do a
LEFT JOIN it means that we will be keeping every row from the “left table” even if there is no corresponding row from the “right table.” This might seem strange with a recursive join because we will be joining the table to itself but depending what we use for the
ON clause will determine if there is a match for every row. In this case, we are joining the
employee table to itself using the
reports_to column for the left table against the
employee_id for the right table. So, for each row, SQL looks at the value for
reports_to and then compares that value (an integer) to the values in the
employee_id column and when it finds a match between these two values, it joins the rows together.
We are specifically using a
LEFT JOIN here because there is a row (the first one, for
Andrew Adams, the general manager) which doesn’t have an employee that they report to so when the tables get joined, this row will have a blank (null) value for