7.3. Joins

Joining two or more tables of data is a powerful feature in the relational model. The SQL procedure enables you to join tables of information quickly and easily. Linking one piece of information with another piece of information is made possible when at least one column is common to each table. A maximum of 32 tables can be combined using conventional (inner) join techniques, as opposed to a maximum of two tables at a time using outer join techniques.

This chapter discusses a number of join topics including why joins are important, the differences between the various join techniques, the importance of the WHERE clause in creating joins, creating and using table aliases, joining three or more tables of data, outer (left, right, and full) joins, subqueries, and set operations. It is important to recognize that many of these techniques can be accomplished using DATA step programming techniques, but the simplicity and flexibility found in the SQL procedure makes it especially useful, if not indispensable, as a tool for the practitioner.

7.3.1. Why Joins Are Important

As relational database systems continue to grow in popularity, the need to access normalized data stored in separate tables becomes increasingly important. By relating matching values in key columns in one table with key columns in the other table(s), you can retrieve information as if the data were stored in one huge file. The results can provide new and exciting insights into possible data relationships.

7.3.2. Information Retrieval Based on Relationships

Being able to define relationships between multiple tables and retrieve information based on these relationships is a powerful feature of the relational model. A join of two or more tables provides a means of gathering and manipulating data in a single SELECT statement. You join two or more tables by specifying the table names in a SELECT statement. Joins are specified on a minimum of two tables at a time, where a column from each table is used for the purpose of connecting the two tables. Connecting columns should have “like” values and the same column attributes because the join’s success is dependent on these values.

In a typical join, you name the relevant columns in the SELECT statement, you specify the tables to be joined in the FROM clause, and in the WHERE clause you specify the relationship you want revealed. That is, you describe the data subset that you want to produce. To be of use (and of a manageable size) your join needs a WHERE clause to constrain the results and ensure their utility and relevance.

Note:

When you create a join without a WHERE clause, you are creating an internal, virtual table called a Cartesian product. This table can be extremely large because it represents all possible combinations of rows and columns in the joined tables.

7.3.3. Types of Complex Queries

The SQL procedure supports a great number of complex queries (sometimes referred to as join types). From inner joins to left, right, and full outer joins, this chapter provides a comprehensive look at the various forms of SELECT statements that can be used to perform multiple table management. Additional topics and examples include subqueries and set operations such as UNION, INTERSECT, and EXCEPT operations. The next table presents the various types of complex queries available in the SQL procedure.

Types of Complex Queries

Query Type	Description
Cartesian Product or Cross Join	This type of join creates a table representing all the combinations of rows and columns from two or more tables. It is represented by the absence of a WHERE clause.
Inner Joins	This type of join is referred to as a conventional type of join because it only retrieves rows with matching values from two or more tables (maximum of 32 tables).
Equijoin	A join with an equality condition (for example, equal sign “=”) specified between columns in two or more tables.
Non-Equijoin	A join with an inequality condition (for example, NE, >, <) specified between columns in two or more tables.
Reflexive or Self Join	A join that combines a table with itself.
Outer Joins	A join that retrieves rows with matching values while preserving some or all of the unmatched rows from one or both tables.
Left Outer Join	A join that preserves unmatched rows from the left table.
Right Outer Join	A join that preserves unmatched rows from the right table.
Full Outer Join	A join that preserves unmatched rows from the left and right tables.
Subqueries	A query within another query — sometimes referred to as a nested query that retrieves rows from one table based on values in another table.
Simple Subquery	A self-contained and independent query within another query that returns single or multiple values from an inner query.
Correlated Subquery	An outer query that passes value(s) to an inner query that after execution passes the results back to the outer query.
Set Operations	These operators combine or concatenate query results vertically.
UNION	Combines all unique (nonduplicate) rows from both queries.
INTERSECT	Combines all matched rows from the first query with rows in the second query.
EXCEPT	Produces rows from the first query that do not appear in the second query.
OUTER UNION	Concatenates (appends) the results from both queries.