EXCEPT equivalence | INTERSECT & exists | not exists

Asked

Viewed 279 times

2

Would be the EXCEPT | INTERSECT always equivalent to a not exists | exists?

Having the following consultation

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 it would have the same result?

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

In what situations I could not replace the EXCEPT | INTERSECT for not exists | exists or the other way around?

sql sql-server

  • EXCEPT and INTERSECT makes a Union of two select with an action to be executed.(returns distinct lines or not). O exists only checks whether the subquery is true or false.

1 answer

1


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, that can generate similar (or even identical) execution plans for different SQL constructs.

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

Planos de execução para as duas consultas

However, this does not mean that they are similar constructions. In the codes posted in the question it was possible to use INTERSECT to solve 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 the INTERSECT.

For example, if in addition to the product code it is necessary to return also 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 will be no way to get the same result using the INTERSECT.

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

and the following error appears when executing code #4:

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

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.