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
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
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. –
Scale IS NOT DISTINCT FROM in SQL Server –
Surge IS NULL x evaluates to true if and only if x is null. true is a value like all others. the trues on the left and the true on the right will be cross-joined. –
Scale IS [NOT] DISTINCT FROM: learn.microsoft.com/en-gb/sql/t-sql/queries/… –
Heriot MERGE and indexed-views (Postgres' Materialized Views require manual updates, SQL Server's don't). –
Heriot 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
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);
ISNULL()? I couldn't see much difference on a sample data set, but I like the simplicity for multicolumn joins. –
Morrell ISNULL kills index use. e.g. compare the plans for CREATE TABLE #T(X INT UNIQUE); SELECT * FROM #T T1 INNER MERGE JOIN #T T2 ON EXISTS(SELECT T1.X INTERSECT SELECT T2.X);SELECT * FROM #T T1 INNER MERGE JOIN #T T2 ON ISNULL(T1.X,-1) = ISNULL(T2.X,-1);SELECT * FROM #T T1 INNER LOOP JOIN #T T2 ON EXISTS(SELECT T1.X INTERSECT SELECT T2.X);SELECT * FROM #T T1 INNER LOOP JOIN #T T2 ON ISNULL(T1.X,-1) = ISNULL(T2.X,-1);DROP TABLE #T - the merge version can't use the index order and needs a sort and the nested loops scans the inner input rather than seeking it. –
Humankind NOT EXISTS and EXCEPT is optimised better than this version. Although sometimes it is just best to write it out the long winded way. X.QID = Y.QID OR (X.QID IS NULL AND Y.QID IS NULL) –
Humankind 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)
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
Hey it is kind of late to answer that but I got the same question, what I realized is that you must have a record with the ID of 0 in you second table to make this :
SELECT * FROM Y
INNER JOIN X ON ISNULL(Y.QID, 0) = ISNULL(X.QID, 0)
to happen, it actually says if there is none, then use 0. BUT what if Y table does NOT have a record with the ID of 0?
So, I found this method, (and worked for my case):
SELECT
ISNULL(Y.QName, 'ThereIsNone') AS YTableQName
FROM
X
LEFT OUTER JOIN Y ON X.QID = Y.QID
This way you DON'T need a record with 0 ID value in your second table (which is Y in this case and Customers in my case), OR any record at all
UPDATE:
You can also take a look at this post for better understanding.
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`;
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.
© 2022 - 2024 — McMap. All rights reserved.
ISNULL(), so I'd assume SQL Server – NonalignmentX.QID = Y.QIDbefore theAND? – EleonoreeleopteneISNULLfunction; the syntax used here is usually spelledCOALESCE. Then again, MySQL also has the<=>operator, which won't help for this question. – ReplyISNULL()is effectively byassing the index. This is a fairly novice query written poorly: Not hard to fix, but it just bit him. There is construct for this I've given both of them in my answer. – Scale