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

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 

Answer 2

This article has a good discussion on this issue. You can use

SELECT *  FROM Y INNER JOIN X ON EXISTS(SELECT X.QID                         INTERSECT                         SELECT y.QID); 

Answer 3

If you want null values to be included from Y.QID then Fastest way is

SELECT * FROM Y LEFT JOIN X ON y.QID = X.QID

Note: this solution is applicable only if you need null values from Left table i.e. Y (in above case).

Otherwise INNER JOIN x ON x.qid IS NOT DISTINCT FROM y.qid is right way to do

Answer 4

Are you committed to using the Inner join syntax?

If not you could use this alternative syntax:

SELECT * 
FROM Y,X
WHERE (X.QID=Y.QID) or (X.QUID is null and Y.QUID is null)