CODEWITHSUNDEEP
sqlsql-server
mikenlanggio
mikenlanggio

Reputation: 1137

NOT `column` IN (...) vs `column` NOT IN (...) in SQL

Usually, When I want to Select all of records when column 'ID' NOT have any value in (1,2,3), I will use the query like this:

SELECT * FROM tableA WHERE ID NOT IN (1,2,3)

But today, I tried to use different query like this:

SELECT * FROM tableA WHERE NOT (ID IN (1,2,3))

And surprisingly, to me, it works! Both SQL Server and Oracle databases (other sql maybe works (?)).

I'm wondering, which one should I use?

Upvotes: 2

Views: 721

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

Let's rewrite both queries to use more explicit logic:

SELECT * FROM tableA WHERE ID NOT IN (1,2,3)
SELECT * FROM tableA WHERE ID <> 1 AND ID <> 2 AND ID <> 3;

For the second query:

SELECT * FROM tableA WHERE NOT (ID IN (1,2,3))
SELECT * FROM tableA WHERE NOT (ID = 1 OR ID = 2 OR ID = 3)

By DeMorgan's law, the above becomes:

SELECT * FROM tableA WHERE ID <> 1 AND ID <> 2 AND ID <> 3;

So, your two versions are actually logically equivalent.

Just as a refresher to DeMorgan's Laws:

NOT (p AND q) == NOT p OR NOT q
NOT (p OR q)  == NOT p AND NOT q

Upvotes: 4

Related Questions