ISNULL vs IS NULL in LEFT JOIN

Question

I'm analyzing differences between ISNULL(field,0) = 0 and (field IS NULL OR field = 0) in performance terms. This where is for Table A joined with a LEFT join with Table B.

select * 
from tabA
left join tabB on tabB.id = tabA.foreignID
where ISNULL(tabB.field,0) = 0

select *
from tabA
left join tabB on tabB.id = tabA.foreignID
where ISNULL(tabB.field,0) = 1

select *
from tabA
left join tabB on tabB.id = tabA.foreignID
where ISNULL(tabB.id,0) = 0 OR ISNULL(tabB.field,0) = 1

I have an index on "field", and I know that ISNULL isn't a sargable function so can invalidate the use of the index. But I can't demonstrate it.

Thanks

Answer 1

First, with a join like this, using SELECT * will limit the optimizer's ability to select a good index from either table. It is possible that it will force a scan (all rows are read) where a seek (only a select subset are read) would be better.

Yes, ISNULL and IS NULL will force make you predicates non-sargable. You can create a filtered index for NULLs in a table but not for Left Joins.

Running the query with "Include actual execution plan" turned on you can determine the best way to go. First some sample data.

CREATE TABLE #tabA (foreignID INT PRIMARY KEY);
CREATE TABLE #tabB (ID        INT IDENTITY PRIMARY KEY, Field INT);

INSERT #tabA VALUES(0),(1),(2),(3),(4),(5),(6);
INSERT #tabB(Field) VALUES(0),(0),(1),(1),(3),(4),(6);

Without proper indexes in place, the performance considerations are not a big deal, you will scan all rows in both tables then apply some filters that further slow you down. Below are 6 queries using SARGable and non-SARGable predicates. They won't produce the same result set but will better understand the performance implications for each query.

;-- <1>
select *
from      #tabA AS tabA
left join #tabB AS tabB 
  on      tabB.id=tabA.foreignID
where     ISNULL(tabB.field,0)=0 -- [Expr1003] = Scalar Operator(isnull(#tabB.[Field] as [tabB].[Field],(0)))

;-- <2>
select *
from      #tabA AS tabA
left join #tabB AS tabB 
  on      tabB.id=tabA.foreignID
where     tabB.field = 0
 OR       tabB.field IS NULL -- does a cross join for 49 rows (7 in A * 7 in B)
;-- <3>

select *
from      #tabA AS tabA
left join #tabB AS tabB 
  on      tabB.id=tabA.foreignID
where     tabB.field = 0 -- Does a seek for only 2 rows

Query1 vs Query2

Here we are guaranteed the same results but notice how, in the plan, the ISNULL(x,0)=0 gets an extra filter in the form of a Compute Scalar operation. This will make the first query slower as you add more rows.

Query2 vs Query3

When I drop the ISNULL predicate (Query 3) I get an index seek vs a scan. Query2 is examining 49 rows (7 in A, 7 in b); removing he 'IS NULL' filter allows a seek on Table A. 7 Rows are still scanned from Table A but only 2 (not 7) from Table B. The rows are not cross joined, so only 9 rows are read. Thats a ~5X reduction in rows read. Imagine if both tables had a million rows, Query 2 would scan and cross join all the records for a total of 1,000,000,0000,0000 (1M * 1M) rows.

Query 3 vs Query 4

Here each query has a single predicate. For Query3 the predicate is tabB.field = 0 for Query4 the predicate is tabB.field IS NULL. This is another example of how both ISNULL() (a built-in function) and IS NULL (built-in ANSI SQL predicate logic) both circumvent your ability to leverage indexes.