CODEWITHSUNDEEP
sqlteradata
BellevueBob
BellevueBob

Reputation: 9618

What is the effect of multiple SQL set operators in a single query?

I am trying to understand what is happening with an existing query that has multiple "set" operators. Various web searches I've tried have not revealed anything, nor has my quick review of the Teradata documentation.

Here is a pseudo-code representation of the query I'm looking at:

SELECT column from table1
UNION
SELECT column from table2
UNION
SELECT column from table3
MINUS 
   (select column from table 4)
UNION
SELECT column from table4

I am mostly interested in how the MINUS is processed; does it subtract rows only from the immediately preceding SELECT from table3 or does it subtract from the combined result set? And note, the parentheses in the MINUS expression are in the code I'm looking at.

Furthermore, I'm assuming that the last UNION is not subject to the MINUS operation at all.

I'm sure I could figure this out myself by creating some sample tables, but I don't have access to the database right now (trying to get some work done from home today).

FYI: Teradata MINUS is the same as ANSI EXCEPT.

Upvotes: 3

Views: 5342

Answers (4)

BellevueBob
BellevueBob

Reputation: 9618

JayC's answer gave me a clue on how to search and I was able to find the right Teradata reference. I'll add this for future readers. From Chapter 6, SQL Functions, Operators, Expressions, and Predicates:

The precedence for processing set operators is as follows:
1 INTERSECT
2 UNION and MINUS/EXCEPT
The set operators evaluate from left to right if no parentheses explicitly specify another order.

There is even an example similar to my situation. Here is a link to download the pdf.

Upvotes: 2

JayC
JayC

Reputation: 7141

On oracle http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries004.htm

You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS. All set operators have equal precedence. If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order.

http://www.postgresql.org/docs/current/static/sql-select.html#SQL-EXCEPT

Multiple EXCEPT operators in the same SELECT statement are evaluated left to right, unless parentheses dictate otherwise. EXCEPT binds at the same level as UNION.

However, in postgresql

http://www.postgresql.org/docs/current/static/sql-select.html#SQL-INTERSECT

Multiple INTERSECT operators in the same SELECT statement are evaluated left to right, unless parentheses dictate otherwise. INTERSECT binds more tightly than UNION. That is, A UNION B INTERSECT C will be read as A UNION (B INTERSECT C).

Emphasis, in any of the quotes, was my own doing..

So, it depends upon the implementation.

Upvotes: 6

PinnyM
PinnyM

Reputation: 35533

You can see how Oracle handles it in this sqlfiddle - it applies MINUS to the entire set aggregated until that point. Other DB's work similarly, and odds are Teradata does the same since EXCEPT (which Oracle and Teradata call MINUS) is an ANSI standard operator.

Upvotes: 2

dfb
dfb

Reputation: 13289

I believe most databases proceed from top to bottom, building the set along the way. Ideally, though, you should write the code to reflect the precedence you want, e.g.,

(((
SELECT column from table1
UNION
SELECT column from table2
) a
UNION
SELECT column from table3
) b
MINUS 
   (select column from table 4)
) c
UNION
SELECT column from table4

Upvotes: 0

Related Questions