ISNULL() doesn't return a value when no record returned

Question

In the case where no record matches ICAO='YXTO'

SELECT 1, ISNULL((SELECT ID FROM Location WHERE ICAO='YXTO'),2)

Returns 1,2

Whereas

SELECT 1, (SELECT ISNULL(ID,2) FROM Location WHERE ICAO='YXTO')

Returns 1, NULL

What's happening? Does ISNULL not return a value when there is no record to act on?

Answer 1

That is correct.

In the first case, the subquery is a scalar subquery. When there is no match, the result is NULL. This is passed as an argument to ISNULL(), so the 2 is returned.

In the second case, the subquery is also a scalar subquery. When there is no match, it also returns NULL -- there is no row returned so the ISNULL() never gets called. Nothing catches the NULL, so hence NULL is returned.