X of Table Two should appear in the output. Compare this query's output to Output 3.2
on page 75.
title 'Table One and Table Two';
select * from one, two
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