topic Multiple set operators in SQL in SAS Programming

Multiple set operators in SQL

TashaChapman14 — Wed, 22 Aug 2018 18:59:38 GMT

I just want to double check my thinking on this... When using multiple set operators in SQL in a single SQL statement, are the actions performed incrementally?

For example, I have in my code:

proc sql;
select distinct vv.naics_code
from eds.pt_naic_codes as vv

union

select distinct loc.naics_code 
from eds.pa_location as loc

union

select distinct qcew.naics_code_pt_naic_codes as naics_code
from eds.ps_qcew_data as qcew

except

select sched.naics_code
from eds.ps_scheduling_naics as sched;
quit;

and I want it to work so that it appends the first three tables before excluding the fourth table.

As written, will I be getting what I expect?

Re: Multiple set operators in SQL

ballardw — Wed, 22 Aug 2018 19:53:40 GMT

Parentheses may be your friend to help control "order".

Perhaps:

proc sql;
(
select distinct vv.naics_code
from eds.pt_naic_codes as vv

union

select distinct loc.naics_code 
from eds.pa_location as loc

union

select distinct qcew.naics_code_pt_naic_codes as naics_code
from eds.ps_qcew_data as qcew
)
except

select sched.naics_code
from eds.ps_scheduling_naics as sched;
quit;

Re: Multiple set operators in SQL

PGStats — Wed, 22 Aug 2018 22:09:48 GMT

The most reliable place to look is the SAS documentation which says:

"A query expression with set operators is evaluated as follows.

Each table expression is evaluated to produce an (internal) intermediate result table.
Each intermediate result table then becomes an operand linked with a set operator to form an expression, for example, A UNION B.
If the query expression involves more than two table expressions, then the result from the first two becomes an operand for the next set operator and operand, such as (A UNION B) EXCEPT C, ((A UNION B) EXCEPT C) INTERSECT D, and so on.
Evaluating a query expression produces a single output table.

Set operators follow this order of precedence unless they are overridden by parentheses in the expressions: INTERSECT is evaluated first. OUTER UNION, UNION, and EXCEPT have the same level of precedence."

So yes, the query will perform as you expect.

Note that using the keyword ALL might improve performance a bit, if you don't need to remove duplicates.