SQL 不在不等于查询中显示空值? [英] SQL not displaying null values on a not equals query?

问题描述

这只是出于好奇而提出的问题，但我正在查看数据库并从表中提取数据并查询其中一列.该列有四个可能的值 null、0、1、2.当我运行查询时:

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:

SELECT * FROM STATUS WHERE STATE != '1' AND STATE != '2';

我得到与运行相同的结果:

I get the same results as running:

SELECT * FROM STATUS WHERE STATE = '0';

即查询列的top命令中为空值的行在结果中好像被省略了，SQL中总是出现这种情况吗?

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?

我通过 Oracle SQL Developer 运行我的命令.

I'm running my commands through Oracle SQL Developer.

推荐答案

在几种语言中 NULL 的处理方式不同:大多数人都知道二值逻辑，其中 true 和 false 是布尔表达式中唯一可比较的值(即使是假被定义为 0，真也被定义为其他任何东西).

In several languages NULL is handled differently: Most people know about two-valued logic where true and false are the only comparable values in boolean expressions (even is false is defined as 0 and true as anything else).

在标准 SQL 中，您必须考虑三值逻辑.NULL 不被视为真正的值，您可以称其为未知".因此，如果该值未知，则不清楚在您的情况下 state 是 0、1 还是其他任何值.所以 NULL != 1 结果再次变为 NULL.

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 state is 0, 1, or anything else. So NULL != 1 results to NULL again.

由此得出结论，无论您在哪里过滤可能为 NULL 的内容，您都必须自己处理 NULL 值.请注意，语法也不同:NULL 值只能与 x IS NULL 进行比较，而不是 x = 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 NULL instead of x = NULL. See Wikipedia for a truth table showing the results of logic operations.

这篇关于SQL 不在不等于查询中显示空值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！