Self-joins in SQL
Senior Data Engineer • Contractor / Freelancer • GCP & AWS Certified
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.
Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.
Enjoyed this? Here are some related articles you might find useful:
