Use self-joins when you are looking for relationship among data items in the same table.

Example: find all the authors who live in the same city:

USE pubs

SELECT FirstPerson=au1.au_fname+' '+au1.au_lname,
SecondPerson= au2.au_fname+' '+au2.au_lname
FROM authors au1
JOIN authors au2
ON au1.City=.au2.City
WHERE au1.au_fname+' '+au1.au_lname != au2.au_fname+' '+au2.au_lname
ORDER BY FirstPerson