 |
|
SQL Server Tips by Burleson |
Self join
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 same.
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
dbo.authors 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, dbo.authors.au_lname
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:
Super SQL
Server Systems
Turbocharge Database Performance with C++ External Procedures
ISBN:
0-9761573-2-2
Joseph Gama, P. J. Naughter
http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm |
