Transcript Session 3 - West Virginia University

Chapter 6: Set Operators
6.1: Introduction to Set Operators
6.2: The EXCEPT Operator
6.3: The INTERSECT Operator
6.4: The UNION Operator
6.5: The OUTER UNION Operator
1
Chapter 6: Set Operators
6.1: Introduction to Set Operators
6.2: The EXCEPT Operator
6.3: The INTERSECT Operator
6.4: The UNION Operator
6.5: The OUTER UNION Operator
2
Objectives

3
Describe SQL set operators and modifiers.
Types of Set Operators
Set operators vertically combine rows from two result
sets. There are four set operators:
4
Except
Union
Intersect
Outer Union
Default Behavior of Set Operators
EXCEPT
INTERSECT
UNION
OUTER UNION
5
Columns are matched by
position and must be the
same data type.
Column names in the final
result set are determined
by the first result set.
All columns from both
result sets are selected.
Set Operator Syntax
General form of an SQL query using a set operator:
SELECT …
EXCEPT | INTERSECT | UNION | OUTER UNION <CORR> <ALL>
SELECT …
The set operator operates on the result sets produced
by the two SELECT statements, not on the actual tables
themselves.
6
Types of Set Operators
EXCEPT
Unique rows from the first table that are not found in
the second table are selected.
select *
from one
except
select *
from two;
7
...
Types of Set Operators
INTERSECT
Common unique rows from both tables are selected.
select *
from one
intersect
select *
from two;
8
...
Types of Set Operators
UNION
Unique rows from both tables are selected with columns
overlaid.
select *
from one
union
select *
from two;
9
...
Types of Set Operators
OUTER UNION
 All rows from both tables, unique as well as
non-unique, are selected.
 Columns are not overlaid.
select *
from one
outer union
select *
from two;
10
Modifiers
You can use two modifiers to modify the behavior
of set operators:
 ALL
 CORRESPONDING
11
Modifiers
ALL
 does not remove duplicate rows, and thus avoids an
extra pass through the data. Use the ALL modifier for
better performance when it is possible.
 is not allowed in connection with an OUTER UNION
operator. It is implicit.
12
Modifiers
CORRESPONDING
 overlays columns by name, instead of by position
 removes any columns not found in both tables
when used in EXCEPT, INTERSECT, and UNION
operations
 causes common columns to be overlaid when
used in OUTER UNION operations
 can be abbreviated as CORR.
13
14
6.01 Poll
By default the EXCEPT, INTERSECT, and UNION set
operators remove duplicate rows from the query output.
 True
 False
15
6.01 Poll – Correct Answer
By default the EXCEPT, INTERSECT, and UNION set
operators remove duplicate rows from the query output.
 True
 False
16
17
Chapter 6: Set Operators
6.1: Introduction to Set Operators
6.2: The EXCEPT Operator
6.3: The INTERSECT Operator
6.4: The UNION Operator
6.5: The OUTER UNION Operator
18
Objectives


19
Describe the SQL process when you use
the EXCEPT set operator and keywords.
Use the EXCEPT set operator.
EXCEPT
Unique rows from the first result set that are not found
in the second result set are selected.
20
Business Scenario
Create a report that displays the employee identification
number and job title of the non-Sales staff employees.
Considerations:
21

The orion.Employee_organization table
contains information about all current Orion Star
employees.

The orion.Sales table contains information about
current Sales employees only.
The EXCEPT Operator
You need a query that returns information from rows that
exist in orion.Employee_organization, but
not in orion.Sales. The EXCEPT operator could
be useful.
orion.Employee_organization
non-sales
orion.Sales
22
Flow Diagram: EXCEPT Operator
EXCEPT
CORR
Yes
No
Remove
nonmatching
columns.
ALL
No
Remove
duplicate rows.
Remove
matching rows.
23
End
Yes
The EXCEPT Operator
Display the unique rows in Table ONE that are not found
in Table TWO.
Table ONE
X
1
1
1
2
3
4
6
24
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
except
select *
from two;
s106d01
...
The EXCEPT Operator
The SQL processor removes duplicate rows within
the tables.
Table ONE
X
1
1
1
2
3
4
6
25
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
except
select *
from two;
s106d01
...
The EXCEPT Operator
The SQL processor creates an intermediate result set
by returning the rows that are found only in Table ONE.
Table ONE
X
1
1
1
2
3
4
6
26
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
Intermediate
Results

1
1
2
4
6

a
b
c
e
g
select *
from one
except
select *
from two;
s106d01
...
The EXCEPT Operator
The column names are determined by Table ONE
in the final result set.
Table ONE
X
1
1
1
2
3
4
6
27
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
Final
Results
X
1
1
2
4
6
A
a
b
c
e
g
select *
from one
except
select *
from two;
s106d01
...
The EXCEPT Operator with ALL
Display the rows (duplicates included) that are found
in Table ONE, but not in Table TWO.
Table ONE
X
1
1
1
2
3
4
6
28
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
except all
select *
from two;
s106d02
...
The EXCEPT Operator with ALL
The SQL processor creates an intermediate result set
by returning the rows that are found only in Table ONE.
Table ONE
X
1
1
1
2
3
4
6
29
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
except all
select *
from two;
Intermediate
Results

1
1
1
2
4
6

a
a
b
c
e
g
s106d02
...
The EXCEPT Operator with ALL
The column names are determined by Table ONE
in the final result set.
Table ONE
X
1
1
1
2
3
4
6
30
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
except all
select *
from two;
Final
Results
X
1
1
1
2
4
6
A
a
a
b
c
e
g
s106d02
...
The EXCEPT Operator with CORR
Display the unique rows that exist in Table ONE and
not in Table TWO, based on same-named columns.
Table ONE
X
1
1
1
2
3
4
6
31
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
except corr
select *
from two;
s106d03
...
The EXCEPT Operator with CORR
The SQL processor eliminates any columns not found
in both tables.
Table ONE
X
1
1
1
2
3
4
6
32
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
except corr
select *
from two;
s106d03
...
The EXCEPT Operator with CORR
The SQL processor eliminates duplicate rows.
Table ONE
X
1
1
1
2
3
4
6
33
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
except corr
select *
from two;
s106d03
...
The EXCEPT Operator with CORR
The SQL processor creates an intermediate result set
by returning rows that are found only in Table ONE.
Table ONE
X
1
1
1
2
3
4
6
34
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
except corr
select *
from two;
Intermediate
Results

4
6
s106d03
...
The EXCEPT Operator with CORR
The SQL processor creates an intermediate result set
by returning rows that are found only in Table ONE.
Table ONE
X
1
1
1
2
3
4
6
35
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
except corr
select *
from two;
Final
Results
X
4
6
s106d03
36
6.02 Quiz
What are the results when you combine ALL with CORR?
Table ALPHA
X
1
1
3
4
5
A
x
y
z
v
w
Table BETA
X
1
2
3
3
5
B
x
y
z
v
v
select *
from alpha
except all corr
select *
from beta;
Run the program s106a01 and review the results.
37
s106a01
6.02 Quiz – Correct Answer
What are the results when you combine ALL with CORR?
Table BETA
Table ALPHA
X
1
1
3
4
5
A
x
y
z
v
w
X
1
2
3
3
5
B
x
y
z
v
v
select *
from alpha
except all corr
select *
from beta;
X
1
4
38
Final result set
Step 1: Using ALL with CORR
Step 1
CORR specifies that only same-named columns
be used. ALL specifies that all values of X be
used, including duplicates.
Table ALPHA
X
1
1
3
4
5
39
A
x
y
Z
v
w
Table BETA
X
1
2
3
3
5
B
x
y
z
v
v
select *
from alpha
except all corr
select *
from beta;
...
Step 2: Using ALL with CORR
Step 2
EXCEPT specifies that only X values found in
ALPHA and not in BETA are used.
Table ALPHA
X
1
1
3
4
5
Table BETA
A
x
y
Z
v
w
X
1
2
3
3
5
B
x
y
z
v
v
select *
from alpha
except all corr
select *
from beta;
X
1 Final result set
4
40
Business Scenario (Review)
Create a report that displays the employee identification
number and job title of the employees who are not Sales
staff.
orion.Employee_organization
non-sales
proc sql;
select Employee_ID, Job_Title
from orion.Employee_organization
except all
select Employee_ID, Job_Title
from orion.Sales;
quit;
orion.Sales
41
s106d04
The EXCEPT Operator
Partial PROC SQL Output (10 out of 259)
Non-Sales Staff Employees
Employee_ID
Job_Title
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
120101 Director
120104 Administration Manager
120105 Secretary I
120106 Office Assistant II
120107 Office Assistant III
120108 Warehouse Assistant II
120109 Warehouse Assistant I
120110 Warehouse Assistant III
120111 Security Guard II
120112 Security Guard I
42
43
6.03 Quiz
Answer the questions about the following program:
select Employee_ID, Job_Title
from orion.Employee_organization
except all
select Employee_ID, Job_Title
from orion.Sales;
1. Why is the CORR keyword not used in this example?
2. Would adding the CORR keyword to this example
change the outcome?
44
s106a02
6.03 Quiz – Correct Answer
Answer the questions about the following program:
select Employee_ID, Job_Title
from orion.Employee_organization
except all
select Employee_ID, Job_Title
from orion.Sales;
1. Why is the CORR keyword not used in this example?
Both SELECT lists specify the same column names
in the same order, so CORR is not necessary.
45
6.03 Quiz – Correct Answer
Answer the questions about the following program:
select Employee_ID, Job_Title
from orion.Employee_organization
except all
select Employee_ID, Job_Title
from orion.Sales;
1. Why is the CORR keyword not used in this example?
Both SELECT lists specify the same column names
in the same order, so CORR is not necessary.
2. Would adding the CORR keyword in this example
change the outcome?
No, adding CORR produces the same results.
46
Using the CORR Keyword
This demonstration illustrates the use of the
CORR keyword with the EXCEPT set operator.
47
s106d04a
The EXCEPT Operator
This query can easily become an in-line view used to
determine how many managers, who are not Sales staff,
are employed at Orion Star.
proc sql;
select count(*) 'No. Non-Sales Managers'
from (select distinct Manager_ID
from orion.Employee_organization
except all
select Employee_ID
from orion.Sales)
;
quit;
 A manager might have multiple direct reports, so use the
48
DISTINCT keyword in the first part of the query. You can
confidently use the ALL keyword because the first query
returns distinct values, and the Sales table contains no
duplicate records.
s106d05
The EXCEPT Operator
PROC SQL Output
No.
Non-Sales
Managers
ƒƒƒƒƒƒƒƒƒ
48
49
50
6.04 Poll
By default, the EXCEPT set operator selects all the rows
from the first result set that are not in the second result
set.
 True
 False
51
6.04 Poll – Correct Answer
By default, the EXCEPT set operator selects all the rows
from the first result set that are not in the second result
set.
 True
 False
By default, the EXCEPT operator eliminates
duplicate rows first. It selects only unique rows from
the first result set that are not in the second result
set.
52
53
Chapter 6: Set Operators
6.1: Introduction to Set Operators
6.2: The EXCEPT Operator
6.3: The INTERSECT Operator
6.4: The UNION Operator
6.5: The OUTER UNION Operator
54
Objectives


55
Describe the SQL process when using the
INTERSECT set operator and keywords.
Use the INTERSECT set operator.
INTERSECT
Common unique rows from both result sets are selected.
56
Business Scenario
Orion Star frequently hires experienced Sales staff
at higher levels on the assumption that they will be
more productive than inexperienced personnel.
Create a report that displays the employee identification
number of current Level III and Level IV Sales staff hired
in 2004, who made at least one sale by the end of 2005.
Considerations:
57

The orion.Order_fact table contains
information on all sales.

The orion.Sales table contains information
about current Sales employees, including job titles
and hire dates.
The INTERSECT Operator
You need a query that returns information from rows that
exist in both orion.Sales and orion.Order_fact.
The INTERSECT operator could be useful.
orion.Sales
sales by
Sales staff
orion.Order_fact
58
Flow Diagram: INTERSECT Operator
INTERSECT
CORR
Yes
No
Remove
nonmatching
columns.
ALL
No
Remove
duplicate rows.
Save
matching rows.
59
End
Yes
The INTERSECT Operator
Display the unique rows common to Table ONE and
Table TWO.
Table ONE
X
1
1
1
2
3
4
6
60
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
intersect
select *
from two;
s106d06
...
The INTERSECT Operator
The SQL processor removes duplicate rows within
the tables.
Table ONE
X
1
1
1
2
3
4
6
61
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
intersect
select *
from two;
s106d06
...
The INTERSECT Operator
The SQL processor creates an intermediate result set
by returning the rows that are found in both tables.
Table ONE
X
1
1
1
2
3
4
6
62
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
Intermediate
Results
 
3 v
select *
from one
intersect
select *
from two;
s106d06
...
The INTERSECT Operator
The column names are determined by Table ONE
in the final result set.
Table ONE
X
1
1
1
2
3
4
6
63
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
Final
Results
X A
3 v
select *
from one
intersect
select *
from two;
s106d06
64
6.05 Quiz
Submit the program s106a03 and review the results. Add
the ALL keyword to the second PROC SQL step and resubmit.
Will the addition of the ALL keyword have any effect on
the output?
Table BBB
Table AAA
X A
select *
X B
1 a
from aaa
1 a
1 a
intersect all
1 a
1 b
select *
2 z
2 c
from bbb;
3 z
3 v
4 e
6 g
65
3 v
5 w
s106a03
6.05 Quiz – Correct Answer
Will the addition of the ALL keyword have any effect on
the output?
Yes. There are duplicate rows common to both tables.
The ALL keyword will include the duplicate rows.
Table AAA
X
1
1
1
2
3
4
6
66
A
a
a
b
c
v
e
g
Table BBB
X
1
1
2
3
3
5
B
a
a
z
z
v
w
select *
from aaa
intersect all
select *
from bbb;
Final
Results
X A
1 a
1 a
3 v
s106a03
The INTERSECT Operator with CORR
Display the unique rows common to Table ONE and
Table TWO, based on the same-named columns.
Table ONE
X
1
1
1
2
3
4
6
67
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
intersect corr
select *
from two;
s106d07
...
The INTERSECT Operator with CORR
The SQL processor eliminates any columns not found
in both tables.
Table ONE
X
1
1
1
2
3
4
6
68
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
intersect corr
select *
from two;
s106d07
...
The INTERSECT Operator with CORR
The SQL processor eliminates duplicate rows and rows
that are not common to Table ONE and Table TWO.
Final
Table ONE
Table TWO
Results
X
1
1
1
2
3
4
6
69
A
a
a
b
c
v
e
g
X
1
2
3
3
5
B
x
y
z
v
w
X
1
2
3
select *
from one
intersect corr
select *
from two;
s106d07
Business Scenario (Review)
Create a report that displays the employee identification
number of current Level III and Level IV Sales staff hired
in 2004, who made at least one sale by the end of 2005.
orion.Sales
sales by
Sales staff
proc sql;
select Employee_ID
from orion.Sales
where year(Hire_date)=2004
and scan(Job_Title,-1) in
("III","IV")
intersect all
select distinct Employee_ID
from orion.Order_fact
where year(Order_date) le 2005;
quit;
orion.Order_fact
70
s106d08
The INTERSECT Operator
PROC SQL Output
Employee ID
ƒƒƒƒƒƒƒƒƒƒƒƒ
120179
71
72
Exercise
This exercise reinforces the concepts discussed
previously.
73
Chapter 6: Set Operators
6.1: Introduction to Set Operators
6.2: The EXCEPT Operator
6.3: The INTERSECT Operator
6.4: The UNION Operator
6.5: The OUTER UNION Operator
74
Objectives


75
Describe the SQL process when you use the UNION
set operator and keywords.
Use the UNION set operator.
UNION
Both result sets are combined, and then unique rows are
selected with columns overlaid.
76
Business Scenario
The management team needs a payroll report for Level I,
II, and III Orion Star employees. The UNION operator
could be useful here. Below is a sketch of the desired
report:
Payroll Report for Level I, II, and III Employees
_______________________________________
Total Paid to ALL Level I Staff
1,234,567
Total Paid to ALL Level II Staff
1,456,789
Total Paid to ALL Level III Staff 2,123,456
77
Flow Diagram: UNION Operator
UNION
CORR
Yes
No
Remove
nonmatching
columns.
Concatenate
tables.
ALL
No
Remove
duplicate rows.
78
End
Yes
The UNION Operator
Display all of the unique rows from both Table ONE and
Table TWO.
Table ONE
X
1
1
1
2
3
4
6
79
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
union
select *
from two;
s106d09
...
The UNION Operator
The SQL processor creates an intermediate result set
by concatenating and sorting Table ONE and Table TWO.
Intermediate
Results
Table ONE
Table TWO
X
1
1
1
2
3
4
6
80
A
a
a
b
c
v
e
g
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
union
select *
from two;

1
1
1
1
2
2
3
3
3
4
5
6

a
a
b
x
c
y
v
v
z
e
w
g
s106d09
...
The UNION Operator
The SQL processor removes duplicate rows from
the intermediate result.
Intermediate
Results
Table ONE
Table TWO
X
1
1
1
2
3
4
6
81
A
a
a
b
c
v
e
g
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
union
select *
from two;

1
1
1
1
2
2
3
3
3
4
5
6

a
a
b
x
c
y
v
v
z
e
w
g
s106d09
...
The UNION Operator
Final Result Set
Table ONE
X
1
1
1
2
3
4
6
82
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
union
select *
from two;
Final Results
X
1
1
1
2
2
3
3
4
5
6
A
a
b
x
c
y
v
z
e
w
g
s106d09
The UNION Operator with CORR
Display all of the unique rows of same-named columns
in Table ONE and Table TWO.
Table ONE
X
1
1
1
2
3
4
6
83
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
union corr
select *
from two;
s106d10
...
The UNION Operator with CORR
The SQL processor creates an intermediate result set
by concatenating and sorting data from same-named
columns.
Intermediate
Table ONE
Table TWO
Results
X
1
1
1
2
3
4
6
84
A
a
a
b
c
v
e
g
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
union corr
select *
from two;
X
1
1
1
1
2
2
3
3
3
4
5
6
s106d10
...
The UNION Operator with CORR
The SQL processor eliminates duplicate rows.
Table ONE
X
1
1
1
2
3
4
6
85
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
union corr
select *
from two;
Intermediate
Results
X
1
1
1
1
2
2
3
3
3
4
5
6
s106d10
...
The UNION Operator with CORR
Table ONE
X
1
1
1
2
3
4
6
86
A
a
a
b
c
v
e
g
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
union corr
select *
from two;
Final
Results
X
1
2
3
4
5
6
s106d10
Business Scenario (Review)
The management team needs a payroll report for the
Level I, II, and III Orion Star employees.


The orion.Staff table contains the job title and
salary information for all Orion Star employees.
Use the UNION set operator to combine the results
from each query that calculates the total paid to all
Level I, II, and III employees.
Payroll Report for Level I, II, and III Employees
_______________________________________
Total Paid to ALL Level I Staff
1,234,567
Total Paid to ALL Level II Staff
1,456,789
Total Paid to ALL Level III Staff 2,123,456
87
The UNION Operator
proc sql;
select 'Total Paid to ALL Level I Staff',
sum(Salary) format=comma12.
from orion.Staff
where scan(Job_Title,-1, ' ')='I'
union
select 'Total Paid to ALL Level II Staff',
sum(Salary) format=comma12.
from orion.Staff
where scan(Job_Title,-1,' ')='II'
union
select 'Total Paid to ALL Level III Staff',
sum(Salary) format=comma12.
from orion.Staff
where scan(Job_Title,-1,' ')='III';
quit;
88
s106d11
The UNION Operator
PROC SQL Output
Payroll Report for Level I, II, and III Employees
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
Total Paid to ALL Level I Staff
3,582,630
Total Paid to ALL Level II Staff
3,569,580
Total Paid to ALL Level III Staff
2,296,425
89
Set Operators and Keywords: Flow Diagrams
EXCEPT
CORR
INTERSECT
Yes
No
CORR
Remove
nonmatching
columns.
ALL
UNION
Yes
No
Remove
nonmatching
columns.
No
Yes
Remove
nonmatching
columns.
Concatenate
tables.
ALL
No
No
Yes
Remove
duplicate rows.
CORR
Yes
Remove
duplicate rows.
ALL
No
Remove
matching rows.
Save
matching rows.
Remove
duplicate rows.
End
End
End
90
Yes
91
6.06 Poll
Is it more or less efficient to use the ALL keyword in a set
operation?
 More efficient
 Less efficient
92
6.06 Poll – Correct Answer
Is it more or less efficient to use the ALL keyword in a set
operation?
 More efficient
 Less efficient
No de-duplication is required.
93
94
Chapter 6: Set Operators
6.1: Introduction to Set Operators
6.2: The EXCEPT Operator
6.3: The INTERSECT Operator
6.4: The UNION Operator
6.5: The OUTER UNION Operator
95
Objectives



96
Describe SQL OUTER UNION set operators and
keywords.
Use the OUTER UNION set operators.
Compare the SQL set operators to traditional
SAS programming tools.
Business Scenario
Write a query to display the employee ID numbers,
job titles, and salaries for all Administrative staff. The data
that you need is in four separate data sets with identical
structures. The OUTER UNION operator could be useful
here.
97
OUTER UNION


98
All rows from both result sets, unique as well as
non-unique, are selected.
Columns are not overlaid.
The OUTER UNION Operator
Display all data values from Table ONE and Table TWO.
Table ONE
X A
1 a
1 a
1 b
2 c
3 v
4 e
6 g
99
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
outer union
select *
from two;
Final Results
X
1
1
1
2
3
4
6
.
.
.
.
.
A
a
a
b
c
v
e
g
X
.
.
.
.
.
.
.
1
2
3
3
5
B
x
y
z
v
w
s106d12
The OUTER UNION Operator with CORR
Display all data values from Table ONE and Table TWO,
but overlay common columns.
Table ONE
X A
1 a
1 a
1 b
2 c
3 v
4 e
6 g
100
Table TWO
X
1
2
3
3
5
B
x
y
z
v
w
select *
from one
outer union corr
select *
from two;
Final Results
X
1
1
1
2
3
4
6
1
2
3
3
5
A
a
a
b
c
v
e
g
B
x
y
z
v
w
s106d13
Business Scenario (Review)
Write a query to display the employee ID numbers,
job titles, and salaries for all Administrative staff.
Considerations:
The data that you need is in four separate data sets
with identical structures:




101
work.Admin_I
work.Admin_II
work.Admin_III
work.Admin_IV
The OUTER UNION Operator with CORR
The OUTER UNION operator with the CORR keyword
might be useful here.
proc sql;
select *
from work.Admin_I
outer union corr
select *
from work.Admin_II
outer union corr
select *
from work.Admin_III
outer union corr
select *
from work.Admin_IV;
102
s106d14
The OUTER UNION Operator Results
PROC SQL Output
Employee
Annual
Employee ID Employee Job Title
Salary
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
120105 Secretary I
$27,110
120992 Office Assistant I
$26,940
120993 Office Assistant I
$26,260
120994 Office Administrator I
$31,645
120106 Office Assistant II
$26,960
120662 Secretary II
$27,045
120749 Office Assistant II
$26,545
120995 Office Administrator II
$34,850
121147 Secretary II
$29,145
120107 Office Assistant III
$30,475
120267 Secretary III
$28,585
120667 Office Assistant III
$29,980
120799 Office Assistant III
$29,070
121146 Secretary III
$29,320
120266 Secretary IV
$31,750
120996 Office Assistant IV
$32,745
103
SQL versus Traditional SAS Programming
The following programs produce the same report:
data three;
set one two;
run;
proc print data=three noobs;
run;
proc sql;
select * from one
outer union corr
select * from two;
quit;
proc append base=one data=two;
run;
proc print data=one noobs;
run;
104
s105d15
105
6.07 Multiple Choice Poll
What DATA step statement yields the same results
as OUTER UNION CORR?
a. MERGE
b. APPEND
c. SET
d. STACK
106
6.07 Multiple Choice Poll – Correct Answer
What DATA step statement yields the same results
as OUTER UNION CORR?
a. MERGE
b. APPEND
c. SET
d. STACK
107
SQL Set Operators versus the DATA Step
Key Points
SQL
DATA Step
Number of tables
processed
simultaneously
Limited to two tables
Not limited by
SAS; limited only
by system resources.
Column handling
Depends on the SET
All columns from all
operator and keywords data sets are included
in output data set(s),
unless specified using
data set options or
program logic.
Duplicate row handling Depends on the SET
All rows are output
operator and keywords unless specified using
data set options or
program logic.
108
 Also consider PROC APPEND for vertical table combination.
Exercise
This exercise reinforces the concepts discussed
previously.
109
Chapter Review
1. How many rows will this query produce?
proc sql;
select *
from table1
INTERSECT
select *
from table2
;
quit;
110
Table 1
ID
1
2
3
Var
Abc
Def
Ghi
Table 2
ID
1
2
3
Var
Abc
Zxy
Ghi
Chapter Review Answers
1. How many rows will this query produce?
Two
Table 1
Table 2
proc sql;
ID Var
ID Var
select *
1 Abc
1 Abc
from table1
2 Def
2 Zxy
INTERSECT
3 Ghi
3 Ghi
select *
from table2
;
PROC SQL Output
quit;
ID
Var
ƒƒƒƒƒƒƒƒƒ
1
Abc
3
Ghi
111
Chapter Review
2. How many rows will this query produce?
proc sql;
select *
from table1
EXCEPT
select *
from table2
;
quit;
112
Table 1
ID
1
2
3
Var
Abc
Def
Ghi
Table 2
ID
1
2
3
Var
Abc
Zxy
Ghi
Chapter Review Answers
2. How many rows will this query produce?
One
Table 1
Table 2
proc sql;
ID Var
ID Var
select *
1 Abc
1 Abc
from table1
2 Def
2 Zxy
EXCEPT
3 Ghi
3 Ghi
select *
from table2
;
PROC SQL Output
quit;
ID
Var
ƒƒƒƒƒƒƒƒƒ
2
Def
113
Chapter Review
3. Will the addition of the CORR and ALL keywords
change the number of rows that this query produces?
proc sql;
select *
from table1
EXCEPT CORR ALL
select *
from table2
;
quit;
114
Table 1
ID
1
2
3
Var
Abc
Def
Ghi
Table 2
ID
1
2
3
Var
Abc
Zxy
Ghi
Chapter Review Answers
3. Will the addition of the CORR and ALL keywords
change the number of rows that this query produces?
No
proc sql;
select *
from table1
EXCEPT CORR ALL
select *
from table2
;
quit;
115
Table 1
ID
1
2
3
Var
Abc
Def
Ghi
Table 2
ID
1
2
3
PROC SQL Output
ID
Var
ƒƒƒƒƒƒƒƒ
2
Def
Var
Abc
Zxy
Ghi
Chapter Review
4. How many columns will this query produce?
proc sql;
select *
from table1
OUTER UNION CORR
select *
from table2
;
quit;
116
Table 1
ID
1
2
3
Var1
Abc
Def
Ghi
Table 2
ID
1
2
3
Var2
Abc
Zxy
Ghi
Chapter Review Answers
4. How many columns will this query produce?
Three
Table 1
Table 2
proc sql;
ID Var1
ID Var2
select *
1 Abc
1 Abc
from table1
2 Def
2 Zxy
OUTER UNION CORR
3 Ghi
3 Ghi
select *
from table2
PROC SQL Output
;
ID Var Var2
quit;
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
1 Abc
2 Def
3 Ghi
1
Abc
2
Xyz
3
Ghi
117