Equivalence between EXCEPT | INTERSECT & exists | not exists

Question

Equivalence between EXCEPT | INTERSECT & exists | not exists

Navigation

#1 by (1 votes)

2

Would it be EXCEPT | INTERSECT always equivalent to not exists | exists ?

Having the following query

SELECT ProductID   
FROM Production.Product  
INTERSECT  
SELECT ProductID   
FROM Production.WorkOrder

I could write as follows

SELECT ProductID   
FROM Production.Product a
where exists (
    SELECT NULL   
    FROM Production.WorkOrder b where a.ProductID = b.ProductID)

And would it have the same result?

In terms of performance between the two queries, which one would perform better? Using exists ? or Using INTERSECT ?

In what situations could I not replace EXCEPT | INTERSECT with not exists | exists or otherwise?

sql sql-server

asked by anonymous 01.12.2017 / 16:45

1 answer

maxlength in currency mask Determine an amount of time to respond to something in Batch

score 1 · Accepted Answer

The question of performance depends on several factors, such as information to be returned, presence of indexes that facilitate the processing of queries, existing and updated statistics, data characteristics, etc. Aside from the action of the query optimizer, which can generate similar (or even identical) execution plans for different SQL constructs.

The queries that are contained in the question are for the Adventure Works database. When executing the two in the same environment, it is observed that the same execution plan was generated for the two queries.

However, this does not mean that they are similar constructs. In the codes posted in the question it was possible to use INTERSECT to resolve one of the queries because only the ProductID column is returned. However, if it is necessary to return some other column of the Production.Product table that does not have equivalence in the Production.WorkOrder table, it will not be possible to use INTERSECT.

For example, if, in addition to the product code, you must also return the product name, as in the code below

-- código #3
SELECT ProductID, Name 
FROM Production.Product a
where exists (
    SELECT NULL   
    FROM Production.WorkOrder b where a.ProductID = b.ProductID);

There is no way to get the same result using INTERSECT.

-- código #4
SELECT ProductID, Name  
FROM Production.Product  
INTERSECT  
SELECT ProductID   
FROM Production.WorkOrder;

The following error is displayed when executing code # 4:

Message 205, Level 16, State 1, Line 2
All merged queries that use the UNION, INTERSECT, or EXCEPT operator must have an equal number of expressions in their destination lists.