When does a query/subquery return a NULL and when no value at all?
- by AspOnMyNet
a) 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?
b) I assume a scalar subquery will always return NULL, when no
matching rows are found? I assume most-outer scalar query also returns
NULL if no rows are found?
c)
SELECT FirstName, LastName, YEAR(BirthDate)
FROM Persons
WHERE YEAR(BirthDate) IN (SELECT YearReleased FROM Albums);
If subquery finds no results, is then a WHERE clause of an outer
query translated into WHERE YEAR(BirthDate) IN (null); ?
If instead WHERE clause is translated into WHERE YEAR(BirthDate) IN(); then shouldn’t that be an error condition, since how can YEAR(BirthDate) value be compared to nothing?
thanx
