SQL Inner Join On Null Values

问题:

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

回答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

回答2:

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)

回答3:

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);

回答4:

I'm pretty sure that the join doesn't even do what you want. If there are 100 records in table a with a null qid and 100 records in table b with a null qid, then the join as written should make a cross join and give 10,000 results for those records. If you look at the following code and run the examples, I think that the last one is probably more the result set you intended:

create table #test1 (id int identity, qid int)
create table #test2 (id int identity, qid int)

Insert #test1 (qid)
select null
union all
select null
union all
select 1
union all
select 2
union all
select null

Insert #test2 (qid)
select null
union all
select null
union all
select 1
union all
select 3
union all
select null


select * from #test2 t2
join #test1 t1 on t2.qid = t1.qid

select * from #test2 t2
join #test1 t1 on isnull(t2.qid, 0) = isnull(t1.qid, 0)


select * from #test2 t2
join #test1 t1 on 
 t1.qid = t2.qid OR ( t1.qid IS NULL AND t2.qid IS NULL )


select t2.id, t2.qid, t1.id, t1.qid from #test2 t2
join #test1 t1 on t2.qid = t1.qid
union all
select null, null,id, qid from #test1 where qid is null
union all
select id, qid, null, null from #test2  where qid is null

回答5:

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

回答6:

Basically you want to join two tables together where their QID columns are both not null, correct? However, you aren't enforcing any other conditions, such as that the two QID values (which seems strange to me, but ok). Something as simple as the following (tested in MySQL) seems to do what you want:

SELECT * FROM `Y` INNER JOIN `X` ON (`Y`.`QID` IS NOT NULL AND `X`.`QID` IS NOT NULL);

This gives you every non-null row in Y joined to every non-null row in X.

Update: Rico says he also wants the rows with NULL values, why not just:

SELECT * FROM `Y` INNER JOIN `X`;

回答7:

You could also use the coalesce function. I tested this in PostgreSQL, but it should also work for MySQL or MS SQL server.

INNER JOIN x ON coalesce(x.qid, -1) = coalesce(y.qid, -1)

This will replace NULL with -1 before evaluating it. Hence there must be no -1 in qid.