查询/子查询什么时候返回 NULL，什么时候根本没有值?

2022-01-23 00:00:00 sql subquery sql-server

  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 表示有一些东西需要 SELECTed，尽管该值可以说是缺乏价值".空记录集表示没有任何符合指定条件的选择.

    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.

相关文章