Output 3.31 Combining a Join with a Subquery
The outer query joins the table to itself and determines the distance between the first city
A1 in table A and city B2 (the first city that is not equal to city A1) in Table B. PROC
SQL then runs the subquery. The subquery does another self-join and calculates the
minimum distance between city A1 and all other cities in the table other than city A1.
The outer query tests to see whether the distance between cities A1 and B2 is equal to
the minimum distance that was calculated by the subquery. If they are equal, then a row
that contains cities A1 and B2 with their coordinates and distance is written.
When to Use Joins and Subqueries
Use a join or a subquery anytime that you reference information from multiple tables.
Joins and subqueries are often used together in the same query. In many cases, you can
solve a data retrieval problem by using a join, a subquery, or both. Here are some
guidelines for using joins and queries.
• If your report needs data that is from more than one table, then you must perform a
join. Whenever multiple tables (or views) are listed in the FROM clause, those tables
become joined.
• If you need to combine related information from different rows within a table, then
you can join the table with itself.
• Use subqueries when the result that you want requires more than one query and each
subquery provides a subset of the table involved in the query.
• If a membership question is asked, then a subquery is usually used. If the query
requires a NOT EXISTS condition, then you must use a subquery because NOT
EXISTS operates only in a subquery; the same principle holds true for the EXISTS
condition.
102 Chapter 3 • Retrieving Data from Multiple Tables