Self-joins in SQL

Photo by Ashley Batz on Unsplash

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.

No alt text provided for this image