7

Recently I happened to come across not () in Sql Server and Oracle. Tried different examples comparing NOT condition and not() with different operators such as LIKE, IN etc. I don't see any difference in terms of the resultset and record counts, but wants to confirm with community if both of these are doing the same or any caveat?

Example Queries

select count(*) from Emp where country not in ('ENGLAND UK', 'HAITI', 'IRELAND') 
select count(*) from Emp where not(country  in ('ENGLAND UK', 'HAITI', 'IRELAND'))
Ganesh S
  • 510
  • 11
  • 19
  • In SQL Server, you can get the server to generate execution plans. You could then compare the execution plans for these two queries and see that they're identical. There is similar functionality in Oracle, under a different name. – Damien_The_Unbeliever Jan 06 '17 at 11:15
  • SQL 2014 and 2016: with a literal argument list in the `IN()` clause, no difference in SQL execution plans. if a PK column is involved, it will be a clustered index seek, otherwise a clustered index scan. with a subquery and optimal keys, it will be two scans and a left anti semi join, no difference wherever the `NOT` operator is placed. – Cee McSharpface Jan 06 '17 at 11:24

3 Answers3

4

Difference will be there when you have another condition with AND/OR. It inverts the AND to OR and OR to AND

select 1 where not(1 = 1 or 1 <> 1 )

will be same as

select 1 where (1 <> 1 and 1 = 1 )

and

select 1 where not(1 = 1 and 1 <> 1 )

will be same as

select 1 where (1 <> 1 or 1 = 1 )

and 

select 1 where not(1 = 1) or 1 = 1

will not be same as

select 1 where not(1 = 1 or 1 = 1 )
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
2

Both placements of the NOT keyword are logically equivalent and give the same results. NOT before a bracketed expression inverts the result of that expression, but that is conventional boolean logic and not specific to SQL.

In T-SQL, execution plans are the same in SQL Server 2014 and SQL Server 2016 for your examples (assumption: the table has a primary key and country is not indexed).

There is one caveat especially with the examples you gave: The IN operator behaves differently than one might expect, when it has NULL values in its argument. For Oracle, see here;

for T-SQL, consider this:

select 1 where 'A' in (null)

This will not return a row. Which appears trivial, but:

select 1 where 'A' not in (null)

This statement will not return a row eiher. Now we could argue, if we logically invert the expression from the first statement like this, it definitely should return a row:

select 1 where not ('A' in (null))

But it does not, because IN (NULL) evaluates to neither true nor false.

Community
  • 1
  • 1
Cee McSharpface
  • 8,493
  • 3
  • 36
  • 77
1

In your case, no real difference

Look at this though:

where x1 not in ('a','b','c') 
and x2 not in ('x','y','z')

This can be written as

where not (x1 in ('a','b','c')
           or x2 in ('x','y','z'))

When writing queries, sometimes I use or when I'm identifying what I want to exclude. It's easier to just use not() than to rewrite the or to and ... not ...

JohnHC
  • 10,935
  • 1
  • 24
  • 40