Is there a way to rewrite EXCEPT statements into NOT IN statements in SQL?

Question

I'm a few weeks into learning SQL, and just finished a problem on a homework assignment about using IN and NOT IN. I managed to get the correct answer, however, I used the EXCEPT clause, which we aren't really allowed to use yet. From what I can tell, EXCEPT and NOT IN are very similar statements in SQL, but I can't understand what the difference is. Here's the general format of my query:

SELECT *
FROM table
WHERE x IN (
    SELECT x
    /* ... some subquery*/
    EXCEPT
    SELECT x
    /* ... some other subquery*/ 
)

Is there a way to rewrite this general query without using the EXCEPT statement? How do EXCEPT and NOT IN differ from each other in general?

Edit: This other post seems to have some good information, but it seems to focus on EXISTS and not IN, which have different purposes don't they?

score 4 · Accepted Answer · edited Apr 23 '15 at 08:30

4

This might help you to understand the Difference between Except and NOT IN

EXCEPT operator returns all distinct rows from left hand side table which does not exist in right hand side table.

On the other hand, "NOT IN" will return all rows from left hand side table which are not present in right hand side table but it will not remove duplicate rows from the result.

edited Apr 23 '15 at 08:30

Zohar Peled

79,642
10
69
121

answered Apr 23 '15 at 08:23

Raj

199
1
1
14

score 1 · Answer 2 · answered Apr 23 '15 at 18:07

SQL is a declarative language. As a result, there are many constructs that can translate to the same relational algebra tree / execution plan. For example, you can specify an inner join using INNER JOIN or CROSS JOIN + WHERE or , + WHERE or LEFT/RIGHT JOIN + WHERE.

EXCEPT/ INTERSECT are set operators while IN/NOT IN are predicates. Consider your example,

SELECT *
FROM table
WHERE x IN (
    SELECT x
    /* ... some subquery*/
    EXCEPT
    SELECT x
    /* ... some other subquery*/ 
)

This can be written using IN / NOT IN like:

SELECT *
FROM table as t1
WHERE t1.x IN (
    SELECT t2.x
    FROM t2
    WHERE t2.x NOT IN (
                      SELECT t3.x
                      FROM t3
    )
)

Now, in terms of syntax these mean the same but semantically they may not produce the same results. For example, if column X is nullable then NOT IN will produce different results.

Lastly, for the simple case if you look at the execution plan in SQL Server Management Studio for these two queries you will find that they use similar plans / join strategies. This is because SQL Server will translate the syntax to a relational tree that is optimized to produce an execution plan. After this translation the final plan may be identical for different queries.

Is there a way to rewrite EXCEPT statements into NOT IN statements in SQL?

2 Answers2