Search code examples
sqlazure-sql-databasesql-except

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

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?

Solution

  • 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.