什么时候查询/子查询返回 NULL，什么时候根本没有值? [英] When does a query/subquery return a NULL and when no value at all?

问题描述

1. 如果查询/子查询没有找到任何匹配的行，那么它要么返回 NULL 要么根本没有值，因此甚至不是 NULL 值.基于查询/子查询返回 NULL 的条件是什么?何时不返回任何结果，甚至不返回 NULL 值?

1. If a query/subquery doesn’t find any matching rows, then it either returns NULL or no value at all, thus not even a NULL value. Based on what criteria does a query/subquery return a NULL and when doesn’t it return any results, not even a NULL value?

标量子查询是否总是返回NULL，当没有时找到匹配的行?我假设最外层标量查询也返回如果未找到行，则为 NULL?

Will a scalar subquery always return NULL, when no matching rows are found? I assume most-outer scalar query also returns NULL if no rows are found?

SELECT FirstName, LastName, YEAR(BirthDate)
FROM Persons
WHERE YEAR(BirthDate) IN (SELECT YearReleased FROM Albums);

• 如果子查询没有找到结果，就是外层的WHERE子句查询转换为 WHERE YEAR(BirthDate) IN (null);?

  如果 WHERE 子句被翻译成 WHERE YEAR(BirthDate) IN();，那不应该是一个错误条件，因为 YEAR(BirthDate) 值比什么都没有?

  If WHERE clause is translated into WHERE YEAR(BirthDate) IN(); instead, shouldn’t that be an error condition, since how can YEAR(BirthDate) value be compared to nothing?

  推荐答案

  当 YearReleased 为 NULL 时，子查询只会返回 NULL，否则会有一个空的记录集，使它成为你提到的 IN () 情况.

  The subquery would only ever return NULL when YearReleased was NULL, otherwise there would be an empty recordset, making it the IN () case you mentioned.

  区分这两者非常重要，因为它们的含义完全不同.NULL 表示有一些东西要被 SELECT 编辑，尽管可以这么说该值表示缺乏价值".空记录集表示没有符合指定条件的选择.

  It's very important to distinguish between the two as they mean entirely different things. NULL indicates that there was something to be SELECTed, although that value indicates a "lack of value" so to speak. An empty recordset indicates that there was nothing to be selected that matched the criteria specified.

  编辑:更新以显示示例结果

  前两个查询只是为了显示两个表中的内容.第三个查询是您的查询，第四个查询仅表明如果您用 NULL 替换子查询，它会产生等效的结果(无行).最后一个查询只是为了表明子查询本身只返回一个很大的 NULL 列表.

  First two queries are just to show what's in the two tables. Third query is your query and the fourth query just shows that it produces an equivalent result (no rows) if you replace the subquery with a NULL. Last query is just to show that the subquery itself just returns a big list of NULLs.

  这篇关于什么时候查询/子查询返回 NULL，什么时候根本没有值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！