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

When does a query/subquery return a NULL and when no value at all?

提问by AspOnMyNet

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?

2. 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?

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

   • If the subquery finds no results, is the WHERE clause of the outer query translated into WHERE YEAR(BirthDate) IN (null);?

   • 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?

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

2. 当没有找到匹配的行时，标量子查询是否总是返回 NULL？我假设如果没有找到行，最外层标量查询也会返回 NULL？

3. 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)value怎么能比什么都没有？

回答by Daniel DiPaolo

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

子查询只会NULL在YearReleasedwas时返回NULL，否则会有一个空的记录集，这就是IN ()你提到的情况。

It's very important to distinguish between the two as they mean entirely different things. NULLindicates 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表示有一些东西要SELECT编辑，尽管这个值可以说是“缺乏价值”。空记录集表示没有与指定条件匹配的要选择的内容。

EDIT: updated to show example results

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

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. 最后一个查询只是为了表明子查询本身只返回一个很大的NULLs列表。

回答by David

a. If there are no matching rows, then the result set will always be empty. There isn't any special handling for the NULL value.

一种。如果没有匹配的行，则结果集将始终为空。NULL 值没有任何特殊处理。

b. That's not true. If there are no matching rows, then the result set is always empty by definition. The result of a scalar function is not a result set so it will either be NULL or another value.

湾 这不是真的。如果没有匹配的行，则根据定义结果集始终为空。标量函数的结果不是结果集，因此它将是 NULL 或其他值。

c.1. If the subquery doesn't return any rows then the "IN" expression will always return false. The set will not be NULL.

c.1. 如果子查询不返回任何行，则“IN”表达式将始终返回 false。该集不会NULL。

c.2. It is valid to compare YEAR(BirthDate)with an empty set. It will just always return false.

c.2. YEAR(BirthDate)与空集进行比较是有效的。它总是会返回false。

回答by vladtepesh

SELECT FirstName, LastName, YEAR(BirthDate)
FROM Persons a
left join (SELECT YearReleased FROM Albums) b on year(b.YearReleased) = year(a.BirthDate)
where year(b.YearReleased) = year(a.BirthDate)

回答by Kevin Martin

There are several types of subquery in SQL, and which one you get (and the resulting behaviour) depends on the context. If the (subquery)syntax is used, say, as an operand in most expression contexts, this is a scalar subquery. On the other hand, as the right operand of the INoperator, it is a table subquery.

SQL 中有几种类型的子查询，您得到哪一种（以及结果行为）取决于上下文。如果(subquery)语法在大多数表达式上下文中用作操作数，则这是一个标量子查询。另一方面，作为运算IN符的右操作数，它是一个表子查询。

For a scalar subquery: If the subquery returns no rows, the result of the scalar subquery is NULL. If the subquery returns more than one row, it is an error. If the subquery returns one row, the result is the value of the query's (only) column for that row.

对于标量子查询：如果子查询不返回任何行，则标量子查询的结果为NULL。如果子查询返回多于一行，则为错误。如果子查询返回一行，则结果是该行的查询（仅）列的值。

For a table subquery: If the subquery returns no rows, the result is an empty set of values, so for instance the INoperation would always return false. Otherwise, the result is the set of the query's column for all the rows.

对于表子查询：如果子查询不返回任何行，则结果是一组空值，因此例如该IN操作将始终返回 false。否则，结果是所有行的查询列的集合。

So to address your questions individually:

因此，要单独解决您的问题：

1. It depends on the context of the subquery

2. Yes, a scalar subquery always returns NULLif there are no rows found

3. In the context of the INoperation it is a table subquery and so no rows means no values to match against. Note however that, due to the semantics of INif your query returns a row where the query column value is itself NULL, you will have a NULLin your compare set. The INoperation is effectively the ORof equality comparisons between the left operand and each value individually in the right-hand value set. Null values in the set will result in "unknown" rather than true or false, and so the INoperation can only ever return true (if there is a match to some non-null value in the set) or unknown (because the value may or may not match null).

1. 这取决于子查询的上下文

2. 是的，NULL如果没有找到行，标量子查询总是返回

3. 在IN操作的上下文中，它是一个表子查询，因此没有行意味着没有要匹配的值。但是请注意，由于IN如果您的查询返回查询列值为自身的行的语义，则您的比较集中NULL将有 a NULL。该IN操作实际上是OR左侧操作数与右侧值集中的每个值之间的相等比较。集合中的空值将导致“未知”而不是真或假，因此IN操作只能返回真（如果与集合中的某个非空值匹配）或未知（因为该值可能或可能不匹配 null）。