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

Question

Recently I happened to meet not () in Sql Server and Oracle. We tried different examples comparing the NOT and non () conditions with different operators, such as LIKE, IN, etc. I do not see a difference in terms of a set of results and records, but I want to confirm the community if they both do the same or any reservations?

Request Examples

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 produce the same results. NOT before the expression in parentheses inverts the result of this expression, but this is normal logic and does not apply 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 the country not indexed).

There is one caveat, especially with the examples you cited: The IN operator behaves differently than might be expected when its argument contains NULL values. For Oracle, see here ;

for T-SQL, consider this:

 select 1 where 'A' in (null) 

This will not return a string. Which looks trivial, but:

 select 1 where 'A' not in (null) 

This statement does not return a string. Now we can argue that if we logically invert an expression from the first statement like this, it should definitely return a string:

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

But this is not so, because IN (NULL) evaluates neither true nor false .

Answer 2

The difference will be there when you have another condition with AND/OR . It inverts values ​​from AND to OR and OR to AND

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

will be the same as

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

and

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

will be the same as

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

and

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

will not be the same as

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

Answer 3

In your case there is no real difference

Take a look at this though:

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

It 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 determine what I want to exclude. It's easier to use not() than rewriting or in and ... not ...