T-SQL 101: 109 Joining a table to itself (self joins)

One thing you might not have considered is that a table can also be joined to itself.

Imagine that I want a list of employees, but I also want their manager’s details, the employee ID and the name for the manager. Now the problem is they’re both in the same table, but they’re different rows in that same table because a manager is also an employee.

The way I did this in the example is to say that I’m reading from Employees and then joining to Employees i.e. the same table.

Aliases

Aliases for tables are generally optional.

Self-joins are one scenario where a table must have an alias. Now that we’re joining a table to itself, you have to know which copy of the table is which. So an alias is definitely required.

In this case, I’m querying Employees and I’ve used an alias emp and then joined to Employees with an alias mgr.

Query Logic

If I execute this, you’ll notice I’ve got employee ID 1 Kristopher Kemp. The manager ID is also number 1 i.e. also Kristopher Kemp. So in fact, this guy is the top of the stack. He’s a top level manager. He reports to himself. Sydney Hull, Billie Daniel, Kristen Baker, all report to Kristopher Kemp.

And then the next level down, Tiffany Rocha reports to Kristen Baker. Note also, that there’s nothing here that says there is only one person at the top level. Other people might report to themselves.

Hierarchies

These queries are commonly used when some sort of hierarchy is involved. And organizational hierarchies are a good example of this, but it could also be other types of hierarchies like bill of materials for products, or it could also be a general ledger account structure in accounting. these are all very common.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-02-05