What is the difference between NOT condition and NOT() in Oracle and MS SQL Server

Question

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')) 

Answer 1

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.

Answer 2

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 )