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

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