AspOnMyNet AspOnMyNet - 24 days ago 5x
SQL Question

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, 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
      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

alt text

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.