sql server - SQL Inner Join On Null Values

Question

I have a Join

SELECT * FROM Y
INNER JOIN X ON ISNULL(X.QID, 0) = ISNULL(y.QID, 0) 

Isnull in a Join like this makes it slow. It's like having a conditional Join. Is there any work around to something like this? I have a lot of records where QID is Null

Anyone have a work around that doesn't entail modifying the data

question from:https://stackoverflow.com/questions/2202172/sql-inner-join-on-null-values

Answer 1

You have two options

INNER JOIN x
   ON x.qid = y.qid OR (x.qid IS NULL AND y.qid IS NULL)

or easier

INNER JOIN x
  ON x.qid IS NOT DISTINCT FROM y.qid