Joins!

But joins also have some special strengths. For example, the two queries in Figure 8.3 (both search for the names of publishers located in the same city as some author) return identical results.

Figure 8.3. Comparing a Join and a Subquery

However, the join gives more options because you can edit it to display results from both tables, which is not possible with the subquery.

SQL
select pub_name, au_fname, au_lname
from publishers, authors
where publishers.city =
   authors.city
pub_name                    au_fname            au_lname
=========================== =================== ========
Algodata Infosystems        Abraham             Bennet
Algodata Infosystems        Cheryl              Carson [2 rows] ...

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.