Oracle和MS SQL Server中NOT条件和NOT()之间有什么区别 [英] What is the difference between NOT condition and NOT() in Oracle and MS SQL Server

问题描述

最近，我碰巧在Sql Server和Oracle中遇到not().尝试了不同的示例，将NOT条件和not()与不同的运算符(例如LIKE，IN等)进行比较.或任何警告?

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?

示例查询

select count(*) from Emp where country not in ('ENGLAND UK', 'HAITI', 'IRELAND') 
select count(*) from Emp where not(country  in ('ENGLAND UK', 'HAITI', 'IRELAND')) 

推荐答案

NOT关键字的两个放置在逻辑上都是等效的，并且给出相同的结果. NOT在带括号的表达式之前会反转该表达式的结果，但这是常规的布尔逻辑，并不特定于SQL.

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.

在T-SQL中，示例的执行计划在SQL Server 2014和SQL Server 2016中是相同的(假设:表具有主键，并且country未建立索引).

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

特别要注意的是您给出的示例:IN运算符在其参数中具有NULL值时，其行为与预期的不同.对于Oracle，请参见此处;

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;

对于T-SQL，请考虑以下问题:

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

但事实并非如此，因为IN (NULL)的评估结果为既非真非假.

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

这篇关于Oracle和MS SQL Server中NOT条件和NOT()之间有什么区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！