O'Reilly logo

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 3
Retrieving Data from Multiple
Tables
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
Selecting Data from More Than One Table By Using Joins . . . . . . . . . . . . . . . . . . . 74
Overview of Selecting Data from More Than One Table By Using Joins . . . . . . . . 74
Inner Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Specialty Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
Using the Coalesce Function in Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Comparing DATA Step Match-Merges with PROC SQL Joins . . . . . . . . . . . . . . . . 92
Using Subqueries to Select Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
Single-Value Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
Multiple-Value Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
Correlated Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
Testing for the Existence of a Group of Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Multiple Levels of Subquery Nesting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
Combining a Join with a Subquery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
When to Use Joins and Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
Combining Queries with Set Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Working with Two or More Query Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Producing Unique Rows from Both Queries (UNION) . . . . . . . . . . . . . . . . . . . . . 104
Producing Rows That Are in Only the First Query Result (EXCEPT) . . . . . . . . . 105
Producing Rows That Belong to Both Query Results (INTERSECT) . . . . . . . . . . 106
Concatenating Query Results (OUTER UNION) . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Producing Rows from the First Query or the Second Query . . . . . . . . . . . . . . . . . 108
Introduction
This chapter shows you how to perform the following tasks:
select data from more than one table by joining the tables together
use subqueries to select data from one table based on data values from another table
combine the results of more than one query by using set operators
Note: Unless otherwise noted, the PROC SQL operations that are shown in this chapter
apply to views as well as tables. For more information about views, see Chapter 4,
“Creating and Updating Tables and Views,” on page 111.
73

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required