Can you explain me please LEFT JOIN here? I understood only concatinations

Screen Link:

https://app.dataquest.io/m/189/intermediate-joins-in-sql/5/recursive-joins
My Code:

SELECT
    e1.first_name || " " || e1.last_name employee_name,
    e1.title employee_title,
    e2.first_name || " " || e2.last_name supervisor_name,
    e2.title supervisor_title
FROM employee e1
LEFT JOIN employee e2 ON e1.reports_to = e2.employee_id
ORDER BY 1;

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 supervisor_name and supervisor_title.

1 Like