I have query similar to the one below, it joins two tables by field which can have NULL value. The result matchs the data if both tables have the same data or consider the condition as optional.
SELECT B.X, A.Y FROM tableA A INNER JOIN tableB B ON ISNULL(B.level5, A.f05) = A.f05 AND ISNULL(B.level6, A.f06) = A.f06
The best index for table B I could think of is such which
CREATE NONCLUSTERED INDEX [Index_On_Table_B] ON B ( X ASC ) INCLUDE (level5, level6)
It helps a bit and from execution plan I can see the index is used but 90 % of the cost is spent in Nested Loops (Inner join).
Is there any way how to get this query working faster?