||SQL Server Tips by Gama and Naughter
This is a particular case when one
table joins to itself, with one or two aliases to avoid confusion. A
self join can be of any type, as long as the joined tables are the
Example: get the names of the
authors who live in the same city, only if two or more live in that
city, plus the names of the cities.
SELECT DISTINCT TOP 100 PERCENT
dbo.authors.au_lname, dbo.authors.au_fname, dbo.authors.city
FROM dbo.authors INNER JOIN
authors_1 ON dbo.authors.city = authors_1.city AND
dbo.authors.au_fname + ' ' + dbo.authors.au_lname <>
authors_1.au_fname +' ' + authors_1.au_lname
ORDER BY dbo.authors.city,
The INNER JOIN will filter by
the authors who live in the same city and have different names.
Concatenating the first and last names simplifies the logic for
comparing the names for readability but this technique does not
necessarily improve performance.
The above book excerpt is from:
Turbocharge Database Performance with C++ External Procedures
Joseph Gama, P. J. Naughter