select ISNULL(c.name,'any') from (select Name from Orders where ID = '123')
select ISNULL((select Name from Orders where ID = '123'),'any')
Orders table have two columns
1. ID
2. Name
and data in Orders is
ID Name
121 abc
124 def
First Query is not returning any result whereas second query is giving any as result. What is the difference
The first form uses a subquery as a table source, in its FROM clause; it can return between zero and many rows.
For each of the rows that the subquery returns, the ISNULL expression is evaluated. But if the subquery returned no rows, then the final output contains no rows.
The second form uses a SELECT without a FROM clause - which will always produce a result set containing exactly one row. It then also uses a scalar subquery (by introducing a subquery in a location where a scalar value is expected) - that has to either produce zero or one results. If the subquery produces zero results, then NULL is substituted.
So, the differences between the two are that the first query can return between zero and many rows, and the ISNULL expression is evaluated for each row. Whereas the second query always produces exactly one row, and if the subquery returned multiple results, an error is produced.
