Have Geek Brain

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

  • If the query / subquery doesn't find any matching rows, it either returns NULL, or has no value at all, so it doesn't even have a NULL value. Based on what criteria does the query / subquery call return NULL, and when it does not return any results, not even NULL?

  • Does scalar subquery always return NULL when no matching rows are found? I am assuming that most external scalar queries also return NULL if no rows are found?

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

    • If the subquery does not find any results, this WHERE clause of the outer query is translated to WHERE YEAR(BirthDate) IN (null);

      ?

    • If the WHERE clause is converted to WHERE YEAR(BirthDate) IN();

      instead, that error condition shouldn't, so how YEAR(BirthDate)

      can you compare to anything?

+2


a source to share


3 answers


The subquery will NULL

NULL

only return when it YearReleased

was NULL

, otherwise there will be an empty recordset, making it the topic IN ()

you mentioned.

It is very important to distinguish between the two as they mean completely different things. NULL

indicates that there was something that should have been SELECT

ed, although this value indicates a "lack of value," so to speak. An empty recordset indicates that there was nothing to be selected that met the specified criteria.

EDIT : Updated to show examples of results



alt text

The first two queries are just to show what's in the two tables. The 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 NULL

. The last query is just to show that the subquery itself is returning a large list NULL

s.

+4


a source


and. If there are no matching rows then the result set will always be empty. There is no special handling for a NULL value.

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



p. 1. If the subquery does not return any rows, the "IN" expression will always return false. There will be no recruitment NULL

.

p. 2. Indeed, it can be compared YEAR(BirthDate)

to the empty set. It will always always return false.

+2


a source 


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)
+2


a source






More articles:


All Articles