loading..
   English
09:18

Sequence of fulfillment of UNION, EXCEPT, and INTERSECT operators

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. 

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

Console
Execute
  1. --Models and types of products of producer B
  2. SELECT model, type FROM Product WHERE maker='B';
model type
1121 PC
1750 Laptop

Console
Execute
  1. --Laptop models
  2. SELECT model, type FROM Product WHERE type='Laptop';
model type
1298 Laptop
1321 Laptop
1750 Laptop
1752 Laptop

Console
Execute
  1. --PC Model
  2. SELECT model, type FROM Product WHERE type='PC';
model type
1121 PC
1232 PC
1233 PC
1260 PC
2111 PC
2112 PC


Lets prove the first claim. If EXCEPT operation has precedence over UNION operation, then the queries

Console
Execute
  1. SELECT model, type FROM Product WHERE maker='B'
  2. UNION
  3. SELECT model, type FROM Product WHERE type='Laptop'
  4. EXCEPT
  5. SELECT model, type FROM Product WHERE type='PC';
and
Console
Execute
  1. (SELECT model, type FROM Product WHERE maker='B'
  2. UNION
  3. SELECT model, type FROM Product WHERE type='Laptop')
  4. EXCEPT
  5. SELECT model, type FROM Product WHERE type='PC';
should give us different results. However it is not so and we obtain the same resulting set:
model type
1298 Laptop
1321 Laptop
1750 Laptop
1752 Laptop

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

Console
Execute
  1. SELECT model, type FROM Product WHERE type='Laptop'
  2. EXCEPT
  3. SELECT model, type FROM Product WHERE type='PC'
  4. UNION
  5. SELECT model, type FROM Product WHERE maker='B';
and
Console
Execute
  1. (SELECT model, type FROM Product WHERE type='Laptop'
  2. EXCEPT
  3. SELECT model, type FROM Product WHERE type='PC')
  4. UNION
  5. SELECT model, type FROM Product WHERE maker='B';
should produce different results. But in this case as well we obtain one and the same result:
model type
1121 PC
1298 Laptop
1321 Laptop
1750 Laptop
1752 Laptop

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

Lets 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
  1. SELECT model, type FROM Product WHERE maker='B'
  2. UNION
  3. SELECT model, type FROM Product WHERE type='Laptop'
  4. INTERSECT
  5. SELECT model, type FROM Product WHERE type='PC';
and
Console
Execute
  1. (SELECT model, type FROM Product WHERE maker='B'
  2. UNION
  3. SELECT model, type FROM Product WHERE type='Laptop')
  4. INTERSECT
  5. SELECT model, type FROM Product WHERE type='PC';
should give identical results. However we obtain different result sets. The first query gives the result
model type
1121 PC
1750 Laptop
while the second one
model type
1121 PC

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

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100