General form of an SQL
query using a set operator:
SELECT column-1<,
... column-n>
FROM table-1
| view-1<,
... table-n | view-n>
<optional
query clauses>
set-operator <ALL> <CORR>
SELECT column-1<,
... column-n>
FROM table-1
| view-1<,
... table-n | view-n>
<optional
query clauses>;
SELECT
specifies the column(s)
that will appear in the result.
FROM
specifies the table(s)
or view(s) to be queried.
optional query clauses
are used to refine
the query further and include the clauses WHERE, GROUP BY, HAVING,
and ORDER BY.
The query or set operation
contains one semicolon, which is placed after the last SELECT statement.
|
proc sql; select * from sasuser.stress98 union select * from sasuser.stress99;
proc sql; select * from table1 set-operator select * from table2 set-operator select * from table3;
Set Operator
|
Treatment of Rows
|
Treatment of Columns
|
Example
|
---|---|---|---|
EXCEPT
|
Selects unique rows
from the first table that are not found in the second table.
|
Overlays columns based
on their position in the SELECT clause without regard to the individual
column names.
|
proc sql;
select *
from table1
except
select *
from table2; |
INTERSECT
|
Selects unique rows
that are common to both tables.
|
Overlays columns based
on their position in the SELECT clause without regard to the individual
column names.
|
proc sql;
select *
from table1
intersect
select *
from table2; |
UNION
|
Selects unique rows
from both tables.
|
Overlays columns based
on their position in the SELECT clause without regard to the individual
column names.
|
proc sql;
select *
from table1
union
select *
from table2; |
OUTER
UNION
|
Selects all rows from
both tables.
The OUTER UNION operator
concatenates the results of the queries.
|
Does not overlay columns.
|
proc sql;
select *
from table1
outer union
select *
from table2; |
ERROR: Column 1 from the first contributor of EXCEPT is not the same type as its counterpart from the second. |
proc sql;
select *
from table1
set-operator <all> <corr>
select *
from table2;
Keyword
|
Action
|
Used When...
|
---|---|---|
ALL
|
Makes only one pass
through the data and does not remove duplicate rows.
|
You do not care if there
are duplicates.
Duplicates are not possible.
ALL cannot be used with
OUTER UNION.
|
CORR (or CORRESPONDING)
|
Compares and overlays
columns by name instead of by position:
If an alias is assigned
to a column in the SELECT clause, CORR use the alias instead of the
permanent column name.
|
Two tables have some
or all columns in common, but the columns are not in the same order.
|