In the case where no record matches ICAO='YXTO'
SELECT 1, ISNULL((SELECT ID FROM Location WHERE ICAO='YXTO'),2)
SELECT 1, (SELECT ISNULL(ID,2) FROM Location WHERE ICAO='YXTO')
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.