Self-joins in SQL
Let's talk about self-joins in SQL. It's one of the join types that don't have their own keyword, but is more of a concept.
It essentially means you are joining a table with itself to retrieve some result from another row.
Prior to the introduction of window functions, self-joins were much more prevalent - you would, for example, join the table to itself to retrieve the value for the previous day.
When considering using a self-join, be mindful of the performance implications. BigQuery, for example, explicitly lists self-joins as an anti-pattern. That is not to say that the need for self-tables has disappeared, there are still cases where we'd need it.
Let's look at an example. We have a table containing all the employee data, including the id of their manager.
If order to retrieve their manager's name, we'd need to perform a self join, using manager_id in the join condition.
By the way, this particular case can also be solved with a recursive common-table expression.
SELECT
emp.employee_id,
emp.name AS employee_name,
man.employee_id AS manager_id,
man.name AS manager_name
FROM employees emp
LEFT JOIN employees man ON emp.manager_id = man.employee_id
Enjoyed this? Here are some related articles you might find useful: