tags:

views:

714

answers:

4

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

+5  A: 

You have two options

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

or easier

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

Evan Carroll
which is faster on run?
Rico
Certainly, the `IS NOT DISTINCT FROM` can not be slower, so I would go with it. But neither of these should be /slow/. You would have to be talking about a fairly massive data set for it to matter.
Evan Carroll
hmm evan as you can probably understand this is a smaller version of a much larger view. As you stated it what is shown above is a novice problem that has gotten away from me. While i don't want to air my view all over the place do you have a minute to look at it maybe and help me patch the wound...
Rico
sure you can contact me on irc. EvanCarroll on freenode.
Evan Carroll
Am I missing something, or is the "IS NOT DISTINCT FROM" feature not available in SQL Server? From what I can find, it will likely make it into the next release, but it's not in SQL Server 2008.
Garland Pope
A: 

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`;
pr1001
That is incorrect.. if is ay were y.qid = x.qid it will give me only rows that are not null.. I want the rows where they are both null as welll.
Rico
Ok, that wasn't clear to me.
pr1001
let me see if i can come up with a more precise example to clear the issues
Rico
A: 

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)
JohnFx
A: 

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
HLGEM
My Current Join does what I want it just does it inneficiently.
Rico
Out of curiosity why do you want a cross join on the null ids?
HLGEM