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

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

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