Chapter Contents |
Previous |
Next |
The SQL Procedure |
Restrictions: | Joins are limited to 32 tables. |
See also: | FROM Clause and query-expression |
Featured in: | Joining Two Tables , Performing an Outer Join , Joining Three Tables , Producing All the Possible Combinations of the Values in a Column , and Matching Case Rows and Control Rows |
table-name
<<AS> alias>, table-name <<AS>
alias>
<, table-name <<AS> alias>...> |
table-name <INNER> JOIN
table-name
ON sql-expression |
table-name LEFT JOIN table-name ON sql-expression |
table-name RIGHT JOIN table-name ON sql-expression |
table-name FULL JOIN table-name ON sql-expression |
Joining Tables |
Conceptually, when two tables are specified, each row of table A is matched with all the rows of table B to produce an internal or intermediate table. The number of rows in the intermediate table (Cartesian product) is equal to the product of the number of rows in each of the source tables. The intermediate table becomes the input to the rest of the query in which some of its rows may be eliminated by the WHERE clause or summarized by a summary function.
A common type of join is an equijoin, in which the values from a column in the first table must equal the values of a column in the second table.
Table Limit |
Specifying the Rows to Be Returned |
The expression is evaluated for each row from each table in the intermediate table described earlier in Joining Tables . The row is considered to be matching if the result of the expression is true (a nonzero, nonmissing value) for that row.
Note: You can follow the ON clause with a WHERE clause to further subset
the query result. See Performing an Outer Join for an example.
Table Aliases |
Joining a Table with Itself |
Inner Joins |
You can perform an inner join by using a list of table-names separated by commas or by using the INNER, JOIN, and ON keywords.
The LEFTTAB and RIGHTTAB tables are used to illustrate this type of join: The following example joins the LEFTTAB and RIGHTTAB tables to get the Cartesian product of the two tables. The Cartesian product is the result of combining every row from one table with every row from another table. You get the Cartesian product when you join two tables and do not subset them with a WHERE clause or ON clause.
proc sql; title 'The Cartesian Product of'; title2 'LEFTTAB and RIGHTTAB'; select * from lefttab, righttab;The LEFTTAB and RIGHTTAB tables can be joined by listing the table names in the FROM clause. The following query represents an equijoin because the values of Continent from each table are matched. The column names are prefixed with the table aliases so that the correct columns can be selected.
proc sql; title 'Inner Join'; select * from lefttab as l, righttab as r where l.continent=r.continent;The following PROC SQL step is equivalent to the previous one and shows how to write an equijoin using the INNER JOIN and ON keywords.
proc sql; title 'Inner Join'; select * from lefttab as l inner join righttab as r on l.continent=r.continent;
See Joining Two Tables , Producing All the Possible Combinations of the Values in a Column , and Matching Case Rows and Control Rows for more examples.
Outer Joins |
A left outer join, specified with the keywords LEFT JOIN and ON, has all the rows from the Cartesian product of the two tables for which the sql-expression is true, plus rows from the first (LEFTTAB) table that do not match any row in the second (RIGHTTAB) table.
proc sql; title 'Left Outer Join'; select * from lefttab as l left join righttab as r on l.continent=r.continent;A right outer join, specified with the keywords RIGHT JOIN and ON, has all the rows from the Cartesian product of the two tables for which the sql-expression is true, plus rows from the second (RIGHTTAB) table that do not match any row in the first (LEFTTAB) table.
proc sql; title 'Right Outer Join'; select * from lefttab as l right join righttab as r on l.continent=r.continent;
A full outer join, specified with the keywords FULL JOIN and ON, has all the rows from the Cartesian product of the two tables for which the sql-expression is true, plus rows from each table that do not match any row in the other table.
proc sql; title 'Full Outer Join'; select * from lefttab as l full join righttab as r on l.continent=r.continent;See Performing an Outer Join for another example.
Joining More Than Two Tables |
In a three-way join, the sql-expression consists of two conditions: one relates the first table to the second table and the other relates the second table to the third table. It is possible to break this example into stages, performing a two-way join into a temporary table and then joining that table with the third one for the same result. However, PROC SQL can do it all in one step as shown in the next example.
The example shows the joining of three tables: COMM, PRICE, and AMOUNT. To calculate the total revenue from exports for each country, you need to multiply the amount exported (AMOUNT table) by the price of each unit (PRICE table), and you must know the commodity that each country exports (COMM table).
proc sql; title 'Total Export Revenue'; select c.Country, p.Export, p.Price, a.Quantity,a.quantity*p.price as Total from comm c, price p, amount a where c.export=p.export and c.country=a.country;See Joining Three Tables for another example.
Comparison of Joins and Subqueries |
proc sql; select IDNumber, Birth from proclib.payroll where IDNumber in (select idnum from proclib.staff where lname like 'B%'); proc sql; select p.IDNumber, p.Birth from proclib.payroll p, proclib.staff s where p.idnumber=s.idnum and s.lname like 'B%';
Note: PROCLIB.PAYROLL is shown in
Creating a Table from a Query's Result .
Chapter Contents |
Previous |
Next |
Top of Page |
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.