Subqueries vs. Self-Joins?

Many statements in which the subquery and the outer query refer to the same table can be alternatively stated as self-joins. For example, you can find authors who live in the same city as Livia Karsen by using a subquery:

SQL
select au_lname, au_fname, city
from authors
where city in
   (select city
						    from authors
						    where au_fname = 'Livia'
						    and au_lname = 'Karsen')

au_lname             au_fname            city
==================== =================== ===============
Green                Marjorie            Oakland
Stringer             Dirk                Oakland
Straight             Dick                Oakland
Karsen               Livia               Oakland
MacFeather           Stearns             Oakland
[5 rows]

Or you can use a self-join:

SQL
select au1.au_lname, au1.au_fname, au1.city
from authors au1, authors au2
where au1.city = au2.city
						 and au2.au_lname = 'Karsen' ...

Get Practical SQL Handbook, The: Using SQL Variants, Fourth Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.