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.


Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.


This was first published in March 2005

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

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close