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:
SQLselect 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:
SQLselect 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.