SELECT FirstName, LastName, YEAR(BirthDate)
WHERE YEAR(BirthDate) IN (SELECT YearReleased FROM Albums);
WHERE YEAR(BirthDate) IN (null);
WHERE YEAR(BirthDate) IN();
The subquery would only ever return
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