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

1. If a query/subquery doesn’t find any matching rows, t开发者_如何学Gohen 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?

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.

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.

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.

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.

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.

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.2. It is valid to compare YEAR(BirthDate) with an empty set. It will just always return false.

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)

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 IN operator, it is a table subquery.

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.

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

So to address your questions individually:

1. It depends on the context of the subquery

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

3. In the context of the IN operation it is a table subquery and so no rows means no values to match against. Note however that, due to the semantics of IN if your query returns a row where the query column value is itself NULL, you will have a NULL in your compare set. The IN operation is effectively the OR of 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 IN operation 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).