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
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