Execution of ISNULL in SQL Server

Question

Here is how i am using ISNULL condition to check for student address. It works fine but how ISNULL function treat the null codition i.e the second parameter which is display if first condition is null.

Will it calculate Value for second parameter when first condition is not null?

select 
   ...
   ...
   (CASE 
      WHEN st.ADDRESS='Y' THEN st.LOCATION
        ELSE 
          ISNULL(
                 (SELECT TOP 1 STDLOC.LOCATION FROM STDLOC 
                  INNER JOIN COMLOC ON STKLOC.LOCATION=COMLOC.CODE  AND COMLOC.ADDRESS='Y' 
                  WHERE STDLOC.ZIBCODE=st.ZIBCODE)
                ,(SELECT TOP 1 COMLOC.LOCATION  FROM COMLOC COMLOC.ZIBCODE=st.ZIBCODE))                       
       END
        ) AS STDUDENTLOCATION
   FROM STUDENT st

Are you asking if the second select will only happen if the first select returns null? BTW, selecting TOP 1 without an ORDER BY clause actually means you will get one random record, since there is no guarantee of the order of the rows returned without an ORDER BY clause. — Zohar Peled, Dec 29 '16 at 08:30
if first value is not null Isnull will not check whats inside the second condition?? — Muhammad Nasir, Dec 29 '16 at 08:32

score 4 · Accepted Answer · answered Dec 29 '16 at 08:42

Both queries inside the ISNULL will be executed, even if the first query will return a value.

Here is a simple test I've made:

Create and populate sample table:

DECLARE @T AS TABLE
(
    Col int
)
INSERT INTO @T Values(1),(2)

SELECT ISNULL(
    (SELECT TOP 1 Col FROM @T ORDER BY Col DESC), 
    (SELECT TOP 1 Col FROM @T ORDER BY Col )
)

Execution plan image:

As you can clearly see, the execution plan includes both queries.

score 1 · Answer 2 · answered Aug 05 '20 at 07:31

I also was looking for an answer. After some reading I came out with my own way to check it.

Deviding by zero will give an error, so we can try:

SELECT ISNULL( (SELECT TOP 1 object_id FROM sys.columns), 5 / 0)

This will give correct result. BUT

SELECT ISNULL( (SELECT TOP 0 object_id FROM sys.columns), 5 / 0)

It will throw an error, because result of first query gives NULL so it tries the second query which fails

score -1 · Answer 3 · edited May 23 '17 at 11:46

-1

ISNULL is a T-SQL specific function that will use the specified second parameter as the return value if the first parameter is NULL(https://msdn.microsoft.com/en-us/library/ms184325.aspx).

Use COALESCE function if you want to return the first non-null value from multiple arguments, and this is a standard function that is supported by all types of relational databases.

This POST provide a good answer for the question:

Is Sql Server's ISNULL() function lazy/short-circuited?

edited May 23 '17 at 11:46

Community

1
1

answered Dec 29 '16 at 08:42

bean

249
2
11

While correct, I don't think that was the question. – Zohar Peled Dec 29 '16 at 08:46

Execution of ISNULL in SQL Server

3 Answers3