Selecting the Join Type

Problem

You want to select a specific type for a join in an SQL query. You can use the join type selection to gain precise control over the data that is included in the results of the query.

Solution

Right-click an existing join in an SQL query, and click the appropriate join type in the pop-up menu to select a different join type.

Tasks

Change Join Types in a Sample SQL Query

Examine a sample SQL query in a SAS Data Integration Studio job to see the effects of changing the join types that are used in the query. The sample query contains the tables and columns that are listed in the following table:
Sample Query Data
Source Table 1: POSTALCODES
Source Table 2: UNITEDSTATES
Target Table: State_Data
Name
Code
Name
Capital
Population
Area
Continent
Statehood
Name
Code
Capital
Population
Area
Continent
Statehood
The join condition for the query is POSTALCODES.Name = UNITEDSTATES.Name. The query is depicted in the following display.
Sample SQL Query in a SAS Data Integration Studio Job
Sample SQL Query in a SAS Data Integration Studio Job
Notice that the query contains an inner join and a WHERE statement. These components are included by default when a query is first created. The following table illustrates how the query is affected when you run through all of the available join types in succession:
Results By Join Type
Join Type
Description
Data Included in Results
Implicit or Explicit Status
Inner
Combines and displays only the rows from the first table that match rows from the second table, based on the matching criteria that are specified in the WHERE clause.
50 rows: 50 matches on name column; 0 non-matches
Implicit
Full
Retrieves both the matching rows and the non-matching rows from both tables.
59 rows: 50 matches on name column; 8 non-matches from POSTALCODES (left table); 1 non-match from UNITEDSTATES (right table)
Explicit
Left
Retrieves both the matching rows and the non-matching rows from the left table.
58 rows: 50 matches on name column; 8 non-matches from POSTALCODES (left table)
Explicit
Right
Retrieves both the matching rows and the non-matching rows from the right table.
51 rows: 50 matches on name column; 1 non-match from UNITEDSTATES (right table)
Explicit
Cross
Combines each row in the first table with every row in the second table (creating a Cartesian product of the tables).
2958 rows
Explicit
Union
Selects unique rows from both tables together and overlays the columns. PROC SQL first concatenates and sorts the rows from the two tables, and then eliminates any duplicate rows. See the following display for the results of a sample union join.
109 rows: 58 rows from POSTALCODES (left table); 51 rows from UNITEDSTATES (right table)
Explicit
A section of the View Data window for a sample query that includes a union join is depicted in the following display.
Sample Section from a View of a Union Join
Sample Section from a View of a Union Join
Rows 45 to 51 come from the POSTALCODES table. Rows 52 to 59 come from the UNITEDSTATES table.
These joins are contained in the FROM clause in the SELECT statement, which comes earlier in an SQL query than a WHERE statement. You can often create more efficient query performance by using the proper join type in a SELECT statement than you can by setting conditions in a WHERE statement that comes later in the query.
Last updated: January 16, 2018