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

Selecting Data from More Than One Table By

Using Joins

Overview of Selecting Data from More Than One Table By Using

Joins

The data that you need for a report could be located in more than one table. In order to

select the data from the tables, join the tables in a query. Joining tables enables you to

select data from multiple tables as if the data were contained in one table. Joins do not

alter the original tables.

The most basic type of join is simply two tables that are listed in the FROM clause of a

SELECT statement. The following query joins the two tables that are shown in Output

3.1 on page 74 and creates Output 3.2 on page 75.

proc sql;

title 'Table One and Table Two';

select *

from one, two;

proc sql;

title 'Table One';

select * from one;

title 'Table Two';

select * from two;

quit;

Output 3.1 Table One, Table Two

74 Chapter 3 • Retrieving Data from Multiple Tables

Output 3.2 Cartesian Product of Table One and Table Two

Joining tables in this way returns the Cartesian product of the tables. Each row from the

first table is combined with every row from the second table. When you run this query,

the following message is written to the SAS log:

Log 3.1 Cartesian Product Log Message

NOTE: The execution of this query involves performing one or more Cartesian

product joins that can not be optimized.

The Cartesian product of large tables can be huge. Typically, you want a subset of the

Cartesian product. You specify the subset by declaring the join type.

There are two types of joins:

• Inner Joins return a result table for all the rows in a table that have one or more

matching rows in the other table or tables that are listed in the FROM clause.

• Outer Joins are inner joins that are augmented with rows that did not match with any

row from the other table in the join. There are three types of outer joins: left, right,

and full.

Inner Joins

Overview of Inner Joins

An inner join returns only the subset of rows from the first table that matches rows from

the second table. You can specify the columns that you want to be compared for

matching values in a WHERE clause.

The following code adds a WHERE clause to the previous query. The WHERE clause

specifies that only rows whose values in column X of Table One match values in column

Selecting Data from More Than One Table By Using Joins 75

X of Table Two should appear in the output. Compare this query's output to Output 3.2

on page 75.

proc sql;

title 'Table One and Table Two';

select * from one, two

where one.x=two.x;

Output 3.3 Table One and Table Two Joined

The output contains only one row because only one value in column X matches from

each table. In an inner join, only the matching rows are selected. Outer joins can return

nonmatching rows; they are covered in “Outer Joins” on page 84.

Note that the column names in the WHERE clause are prefixed by their table names.

This is known as qualifying the column names, and it is necessary when you specify

columns that have the same name from more than one table. Qualifying the column

name avoids creating an ambiguous column reference.

Using Table Aliases

A table alias is a temporary, alternate name for a table. You specify table aliases in the

FROM clause. Table aliases are used in joins to qualify column names and can make a

query easier to read by abbreviating table names.

The following example compares the oil production of countries to their oil reserves by

joining the OilProd and OilRsrvs tables on their Country columns. Because the Country

columns are common to both tables, they are qualified with their table aliases. You could

also qualify the columns by prefixing the column names with the table names.

Note: The AS keyword is optional.

libname sql 'SAS-library';

proc sql outobs=6;

title 'Oil Production/Reserves of Countries';

select * from sql.oilprod as p, sql.oilrsrvs as r

where p.country = r.country;

76 Chapter 3 • Retrieving Data from Multiple Tables