Using self joins in SQL Server

Self joins are useful when you have a nested SQL query with the inner and outer queries referencing the same table.

A self join connects two instances of the same table together for a query. SQL Server lets you create self joins...

provided that you include distinguishing references for each of the tables in the form of different names or aliases. When you create a query that uses a self-join you then use those names to refer to the table, instead of the table name itself. A self join is useful when you have a nested SQL query with the inner and outer queries referencing the same table. You might want to have a query that matches a supervisor to employees in an Employee table when that table has identification fields for both the employees and managers.

Most self joins use the equal operator to link the two tables to one another through a primary ID index. However, when you use an equal sign the query will return a result set with the same record listed twice. This duplication arises from matched records going from left to right and right to left, and since the tables are identical and symmetrical you get duplication. If you use an operator other than the equal signs such as the > (greater than) then only one match would be returned.

To create a self join automatically between tables with a reflexive relationship, open the Diagram pane and add another instance of the table you want to use. SQL Server will assign it a different name incrementally adding a number. The Query Designer creates a join line between the two tables.

You can also create a self join manually when the table has no reflexive relationship as you might do if you wanted to find two colleges located in the same city. To manually create the self join, add the second table instance and compare the same column of both instances to each other. You will find that self joins often require additional conditions to remove data that is not of interest. Create the instance in the Diagram pane as before and then right click on the join line and select the Properties command. Then alter the comparison between the primary keys to an operator other than the equal sign.


This was first published in March 2005

Dig Deeper on SQL-Transact SQL (T-SQL)






Forgot Password?


Your password has been sent to: