SQL 不在不等于查询中显示空值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8036691/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL not displaying null values on a not equals query?
提问by Alexei Blue
This is just a question out of curiosity but I am looking at a database and pulling data from a table with a query on one of the columns. The column has four possible values null, 0, 1, 2. When I run the query as:
这只是出于好奇而提出的问题，但我正在查看数据库并从表中提取数据，并对其中一列进行查询。该列有四个可能的值null、0、1、2。当我运行查询时：
SELECT * FROM STATUS WHERE STATE != '1' AND STATE != '2';
I get the same results as running:
我得到与运行相同的结果：
SELECT * FROM STATUS WHERE STATE = '0';
I.e. rows with a null value in the top command in the queried column seem to be omitted from the results, does this always happen in SQL?
即在查询列的顶部命令中具有空值的行似乎从结果中被省略了，这在 SQL 中是否总是发生？
I'm running my commands through Oracle SQL Developer.
我正在通过 Oracle SQL Developer 运行我的命令。
采纳答案by Alex
In several languages NULL is handled differently: Most people know about two-valued logic where trueand falseare the only comparable values in boolean expressions (even is false is defined as 0 and true as anything else).
在几种语言中，NULL 的处理方式不同：大多数人都知道二值逻辑，其中true和false是布尔表达式中唯一可比较的值（即使是假被定义为 0，真被定义为其他任何东西）。
In Standard SQL you have to think about three-valued logic. NULL is not treated as a real value, you could rather call it "unknown". So if the value is unknown it is not clear if in your case stateis 0, 1, or anything else. So NULL != 1results to NULLagain.
在标准 SQL 中，您必须考虑三值逻辑。NULL 不被视为真正的值，您可以称其为“未知”。因此，如果该值未知，则不清楚您的情况state是 0、1 还是其他任何值。所以NULL != 1结果来NULL了。
This concludes that whereever you filter something that may be NULL, you have to treat NULL values by yourself. Note that the syntax is different as well: NULL values can only be compare with x IS NULLinstead of x = NULL. See Wikipedia for a truth table showing the results of logic operations.
这得出的结论是，无论您在哪里过滤可能为 NULL 的内容，您都必须自己处理 NULL 值。请注意，语法也不同：NULL 值只能与x IS NULL而不是进行比较x = NULL。有关显示逻辑运算结果的真值表，请参阅维基百科。
回答by GregM
Yest it's normal, you can maybe put a database settings to fixed that
是的，这是正常的，您可以将数据库设置进行修复
But you could modify your code and do something like that :
但是您可以修改您的代码并执行以下操作：
SELECT * FROM STATUS WHERE STATE != '1' OR STATE != '2' or STATE is null;
Look at this for more info : http://www.w3schools.com/sql/sql_null_values.asp
回答by Michael Durrant
multiple or's with where's can quickly become hard to read and uncertain as to results.
多个or's 和where's 很快就会变得难以阅读并且结果不确定。
I would recommend extra parenthesis plus the use of the INstatement (NOT INin this case), e.g.
我会推荐额外的括号加上IN语句的使用（NOT IN在这种情况下），例如
SELECT * FROM STATUS WHERE (STATE NOT IN ('1', '2')) or STATE is null;
SELECT * FROM STATUS WHERE (STATE NOT IN ('1', '2')) or STATE is null;
Implmentations can vary between database vendor but the above explicit syntax should make sure of the results.
数据库供应商之间的实施可能会有所不同，但上述明确的语法应确保结果。
回答by Madam Zu Zu
use NVLlike so: SELECT * FROM STATUS WHERE NVL(STATE,'X') != '1' AND NVL(STATE,'X')!= '2';
NVL像这样使用：SELECT * FROM STATUS WHERE NVL(STATE,'X') != '1' AND NVL(STATE,'X')!= '2';
回答by Dzmitry Lahoda
I created script to research Oraclebehavior:
我创建了脚本来研究 Oracle行为：
create table field_values ( is_user_deletable VARCHAR2(1 CHAR), resource_mark VARCHAR2(3 CHAR) ) insert into field_values values (NULL, NULL); insert into field_values values ('Y', NULL); insert into field_values values ('N', NULL); select * from field_values; -- 3 row -- 1 row, bad update field_values set resource_mark = 'D' where is_user_deletable = 'Y'; update field_values set resource_mark = 'D' where is_user_deletable <> 'N'; update field_values set resource_mark = 'D' where is_user_deletable != 'N'; update field_values set resource_mark = 'D' where is_user_deletable not in ('Y'); -- 2 rows, good, but needs more SQL and more comparisons. Does DB optimizes? update field_values set resource_mark = 'D' where is_user_deletable = 'N' or is_user_deletable is null; -- it better to have ('Y' and NULL) or ('N' and NULL), but not 'Y' and 'N' and NULL (avoid quires with https://en.wikipedia.org/wiki/Three-valued_logic) -- adding new column which is 'Y' or 'N' only into existing table may be very long locking operation on existing table (or running long DML script)
