Creating and using Outer Joins

An outer join combines rows of data from two tables. There are three types of outer joins:

left join

• returns all matching rows in both tables, in addition to rows in the left table that have no matching rows in the right table.

right join

• returns all matching rows in both tables, in addition to rows in the right table that have no matching rows in the left table.

full join

• returns all matching and nonmatching rows from both tables.

In all three types of outer joins, the columns in the result row that are from the unmatched row are set to missing values.

In this example, you first create an inner join that relates employee identification number and salary. Then, you create an outer join that combines this data with data from another table to compute the gross monthly pay for employees who have taken leave.

Creating a Query View

You can create an SQL view that contains the syntax of your query. For this example, you use a view to create an outer join query.

In the SQL QUERY TABLES window, select SAMPLE.EMPINFO and SAMPLE.SALARY from the Available Tables list and add them to the Selected Tables list. Select OK .

In the SQL QUERY COLUMNS window, select NAME , the two ADDRESS items, Identification Number , Employee number , Salary , BEGDATE , and ENDDATE and add them to the Selected Columns list.

Select
View – Where Conditions for Subset
to display the WHERE EXPRESSION window.

Select EMPINFO.Identification Number from the Available Columns list. Select EQ from the list of operators. Select Salary.Identification Number from the Available Columns list. Select OK.

This WHERE expression creates an inner join of EMPINFO and Salary based on Identification Number. To save the query as a view, select
Tools – Show Query
to display the SQL QUERY window. Select Create View.

Select the right arrow next to the Library field to display a list of SAS libraries.

The list of libraries displayed at your site might be different from the ones in the illustration. Select SAMPLE from the Libraries list. Select OK.

Type MYVIEW in the View field. Select OK to return to the SQL QUERY window. Select Goback to return to the SQL QUERY COLUMNS window.

Creating an Outer Join

Select
Tools – Reset
to reset the query. Select OK in the dialog box that appears.

Select SAMPLE.MYVIEW and SAMPLE.LEAVE from the Available Tables list and add them to the Selected Tables list. Select OK to display the SQL QUERY COLUMNS window.

Select
View – Join Type

Select Matched Join and Unmatched Rows (Outer Join) . Select OK to display the Columns for Setting Join Criteria window.

Select Identification Number from the SAMPLE.MYVIEW Columns (Left) list. Select Identification Number from the SAMPLE.LEAVE Columns (Right) list. Select the down arrow next to Join Type. Select Left from the pop-up menu. Select OK to return to the SQL QUERY COLUMNS window.

Select
View – Distinct
to eliminate duplicate values from your output.

Select NAME , Identification Number , and Employee number from the Available Columns list and add them to the Selected Columns list.

Building a Column Expression

Select Build a Column to display the BUILD A COLUMN EXPRESSION window.

Select MYVIEW.Salary from the Available Columns list. Select / from the list of operators. Select <CONSTANT enter value> from the Available Columns list. Type 12 in the Numeric field. Select OK . Select outside the list of operators to dismiss it.

Select Column Attributes to display the Expression Column Attributes window. Enter monthpay in the Alias Name field. Enter dollar12.2 in the Format field. Enter Employee's Monthly Pay in the Label field.

Select OK to return to the BUILD A COLUMN EXPRESSION window. Select OK to return to the SQL QUERY COLUMNS window.

In the SQL QUERY COLUMNS window, select Build a Column to display the BUILD A COLUMN EXPRESSION window. Select Operators . Select ( from the list of operators.

Select monthpay from the Available Columns list. Select * from the list of operators. Select LEAVE.Payroll percentage from the Available Columns list. Select ) from the list of operators. Select outside the list of operators to dismiss it.

Select Column Attributes to display the Expression Column Attributes window. Enter adjstpay in the Alias Name field. Enter dollar12.2 in the Format field. Enter Employee's Gross Pay in the Label field. Select OK to return to the BUILD A COLUMN EXPRESSION window. Select OK to return to the SQL QUERY COLUMNS window.

Order by Columns

In the SQL QUERY COLUMNS window, select
View – Order By
to display the ORDER BY COLUMNS window.

Select the second Identification Number from the Available Columns list and add it to the Order By Columns list. Select OK to return to the SQL QUERY COLUMNS window.

Viewing Your Output

Select
Tools – Run Query – Run Immediate
to display the results of the query in the Output window.