SAS 9.4 SQL Procedure User's Guide, Third Edition, 3rd Edition

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