Tip

Using self joins in SQL Server

Barrie Sosinsky, Contributor

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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.