SELECT statement

Conventional set-theoretic operations and the SELECT statement

When solving exercise #6 (SELECT) of the rating stage we had to answer the question of precedence of UNION, EXCEPT, and INTERSECT operations.  Martin Gruber [4] in his “SQL Instant Reference” suggests the following logical sequence of their fulfillment:

• UNION, EXCEPT
  
• INTERSECT

If we presume that the logical sequence of fulfillment of operations corresponds to their precedence, than the precedence of UNION and EXCEPT operations is identical and, as a result, they should be fulfilled in accordance with the sequence in which they are recorded, and this sequence is not changed by placement of the brackets. Thereby both of the operations are fulfilled before INTERSECT operation, i.e. they have precedence over it. 

Let’s analyze three simple queries, which we will combine in different ways in order to prove the aforementioned claims:

Console

Execute

--Models and types of products of producer B
SELECT model, type FROM Product WHERE maker='B';

--Models and types of products of producer B SELECT model, type FROM Product WHERE maker='B';

    Scheme

Console

Execute

--Laptop models
SELECT model, type FROM Product WHERE type='Laptop';

--Laptop models SELECT model, type FROM Product WHERE type='Laptop';

    Scheme

Console

Execute

--PC Model
SELECT model, type FROM Product WHERE type='PC';

--PC Model SELECT model, type FROM Product WHERE type='PC';

    Scheme

Let’s prove the first claim. If EXCEPT operation has precedence over UNION operation, then the queries

Console

Execute

SELECT model, type FROM Product WHERE maker='B'
UNION
SELECT model, type FROM Product WHERE type='Laptop'
EXCEPT
SELECT model, type FROM Product WHERE type='PC';

SELECT model, type FROM Product WHERE maker='B' UNION SELECT model, type FROM Product WHERE type='Laptop' EXCEPT SELECT model, type FROM Product WHERE type='PC';

    Scheme

Console

Execute

(SELECT model, type FROM Product WHERE maker='B'
UNION
SELECT model, type FROM Product WHERE type='Laptop')
EXCEPT
SELECT model, type FROM Product WHERE type='PC';

(SELECT model, type FROM Product WHERE maker='B' UNION SELECT model, type FROM Product WHERE type='Laptop') EXCEPT SELECT model, type FROM Product WHERE type='PC';

    Scheme

Similarly, if UNION operation has precedence over EXCEPT operation, then the queries

Console

Execute

SELECT model, type FROM Product WHERE type='Laptop'
EXCEPT
SELECT model, type FROM Product WHERE type='PC'
UNION
SELECT model, type FROM Product WHERE maker='B';

SELECT model, type FROM Product WHERE type='Laptop' EXCEPT SELECT model, type FROM Product WHERE type='PC' UNION SELECT model, type FROM Product WHERE maker='B';

    Scheme

Console

Execute

(SELECT model, type FROM Product WHERE type='Laptop'
EXCEPT
SELECT model, type FROM Product WHERE type='PC')
UNION
SELECT model, type FROM Product WHERE maker='B';

(SELECT model, type FROM Product WHERE type='Laptop' EXCEPT SELECT model, type FROM Product WHERE type='PC') UNION SELECT model, type FROM Product WHERE maker='B';

    Scheme

Thus, UNION and EXCEPT operations are equivalent in terms of precedence.

Let’s check precedence of INTERSECT operation as compared to other statements (in test problems one can choose any of them, as both of them have the same sequence).

If INTERSECT is inferior in precedence or equivalent to UNION, then the queries

Console

Execute

SELECT model, type FROM Product WHERE maker='B'
UNION
SELECT model, type FROM Product WHERE type='Laptop'
INTERSECT
SELECT model, type FROM Product WHERE type='PC';

SELECT model, type FROM Product WHERE maker='B' UNION SELECT model, type FROM Product WHERE type='Laptop' INTERSECT SELECT model, type FROM Product WHERE type='PC';

    Scheme

Console

Execute

(SELECT model, type FROM Product WHERE maker='B'
UNION
SELECT model, type FROM Product WHERE type='Laptop')
INTERSECT
SELECT model, type FROM Product WHERE type='PC';

(SELECT model, type FROM Product WHERE maker='B' UNION SELECT model, type FROM Product WHERE type='Laptop') INTERSECT SELECT model, type FROM Product WHERE type='PC';

    Scheme

Conclusion. Logical sequence, suggested in the beginning of the article, does not correspond to the order of precedence of the operations, and, to my mind, it should be reversed:

• INTERSECT
  
• UNION, EXCEPT