Use a join
or a subquery any time 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.
-
Many queries can be formulated
as joins or subqueries. Although the PROC SQL query optimizer changes
some subqueries to joins, a join is generally more efficient to process.