SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


OR in JOIN criteria


OR in JOIN criteria

Author
Message
r5d4
r5d4
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1665 Visits: 977
Apologies if this has been asked before, searching for it yields too many results.

I've been eliminating NOT EXISTS in some code by LEFT JOINing tableX and using a WHERE TableX.JoinColumn IS NULL
Big gains, I'm pleased with that.

My next challenge is OR in a JOIN criteria, so...

SELECT id
FROM table1
LEFT JOIN table2
ON table1.keycolumn = table2.keycolumn
AND (table1.code1 = table2.code1 OR table1.text1 = table2.text1)

Performing 2 SELECT queries and UNIONing them to avoid the OR comes to mind,
The bigger picture is that I want to LEFT JOIN to this subquery to reproduce the NOT EXISTS functionality that performs so badly.
I'm eliminating records that match the crazy OR join.

This is a medical application, I can't change the tables, merely report on them.

Any ideas most appreciated.
Thank you
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)SSC Guru (157K reputation)

Group: General Forum Members
Points: 157142 Visits: 23284
r5d4 - Wednesday, December 13, 2017 10:26 AM
Apologies if this has been asked before, searching for it yields too many results.

I've been eliminating NOT EXISTS in some code by LEFT JOINing tableX and using a WHERE TableX.JoinColumn IS NULL
Big gains, I'm pleased with that.

My next challenge is OR in a JOIN criteria, so...

SELECT id
FROM table1
LEFT JOIN table2
ON table1.keycolumn = table2.keycolumn
AND (table1.code1 = table2.code1 OR table1.text1 = table2.text1)

Performing 2 SELECT queries and UNIONing them to avoid the OR comes to mind,
The bigger picture is that I want to LEFT JOIN to this subquery to reproduce the NOT EXISTS functionality that performs so badly.
I'm eliminating records that match the crazy OR join.

This is a medical application, I can't change the tables, merely report on them.

Any ideas most appreciated.
Thank you

Have you tried using temp tables to break things down? Joining to your index-optimised temp table might speed things up.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Jeffrey Williams 3188
Jeffrey Williams 3188
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56359 Visits: 10456
r5d4 - Wednesday, December 13, 2017 10:26 AM
Apologies if this has been asked before, searching for it yields too many results.

I've been eliminating NOT EXISTS in some code by LEFT JOINing tableX and using a WHERE TableX.JoinColumn IS NULL
Big gains, I'm pleased with that.

My next challenge is OR in a JOIN criteria, so...

SELECT id
FROM table1
LEFT JOIN table2
ON table1.keycolumn = table2.keycolumn
AND (table1.code1 = table2.code1 OR table1.text1 = table2.text1)

Performing 2 SELECT queries and UNIONing them to avoid the OR comes to mind,
The bigger picture is that I want to LEFT JOIN to this subquery to reproduce the NOT EXISTS functionality that performs so badly.
I'm eliminating records that match the crazy OR join.

This is a medical application, I can't change the tables, merely report on them.

Any ideas most appreciated.
Thank you

You could use 2 LEFT JOINS and then determine which one of the 2 values you want to keep using COALESCE.


SELECT ...
, COALESCE(t2.col2, t3.col2) AS col2
, COALESCE(t2.col3, t3.col3) AS col3
, COALESCE(t2.col4, t3.col4) AS col4
FROM table1 t
LEFT JOIN table2 t2 ON t2.col1 = t.col1
LEFT JOIN table2 t3 ON t3.text1 = t.text1


You cannot utilize a UNION ALL - it would have to be a UNION which will require a sort operation to remove duplicates.


Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (272K reputation)SSC Guru (272K reputation)SSC Guru (272K reputation)SSC Guru (272K reputation)SSC Guru (272K reputation)SSC Guru (272K reputation)SSC Guru (272K reputation)SSC Guru (272K reputation)

Group: General Forum Members
Points: 272873 Visits: 33814
Sometimes, not always, an OR causing slow behavior can be replaced by two copies of the query combined using UNION ALL. Not seeing the details here I don't want to say for sure that will improve things, but it can.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
r5d4
r5d4
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1665 Visits: 977
Thank you all,
That's given me some more ideas to try.
sgmunson
sgmunson
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53764 Visits: 5867
FYI, if you have a GROUP BY in the query with the OR in the JOIN, then you have a different kind of problem, and the solution is more likely to involve "pre-generating" a temp table with perhaps some primary key values that can be used to limit records, after indexing said temp table. Of course, there's always more than one way to "skin the cat", so to speak.

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
SoCal_DBD
SoCal_DBD
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2165 Visits: 1706
Grant Fritchey - Thursday, December 14, 2017 5:02 AM
Sometimes, not always, an OR causing slow behavior can be replaced by two copies of the query combined using UNION ALL. Not seeing the details here I don't want to say for sure that will improve things, but it can.

+ 1
They key data in our database involves a very similar type of condition. I did a comparison and ultimately landed on the solution Grant calls out. Far fewer table scans. Overall, much better query plan.

Jeffrey Williams 3188
Jeffrey Williams 3188
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56359 Visits: 10456
SoCal_DBD - Friday, December 22, 2017 10:22 AM
Grant Fritchey - Thursday, December 14, 2017 5:02 AM
Sometimes, not always, an OR causing slow behavior can be replaced by two copies of the query combined using UNION ALL. Not seeing the details here I don't want to say for sure that will improve things, but it can.

+ 1
They key data in our database involves a very similar type of condition. I did a comparison and ultimately landed on the solution Grant calls out. Far fewer table scans. Overall, much better query plan.

If you have an OR condition in the JOIN - and there is no possibility that the values in the OR statement overlap...then you can convert it to a UNION ALL. If the values overlap it would require a UNION which may not improve performance over the JOIN.

The alternatives are an OUTER JOIN or CROSS APPLY - and now that I think about it a CROSS APPLY might just be better.


SELECT ...
FROM table1 t1
CROSS APPLY (SELECT ...
FROM table2 t2
WHERE t2.col1 = t1.col1
OR t2.col2 = t1.col2) As t3
WHERE ...


A normal INNER JOIN with an OR would include the row one time for a match on either condition - if you UNION ALL that same row would be included in both queries and cause duplicate rows to be returned. An OUTER JOIN will match the same row and result in a single row returned - the CROSS APPLY will also match a single row returning only the one row that matches.

If the OR is in the WHERE clause - it can only be modified to a UNION ALL if the OR is performed on the same column. As soon as you have an OR statement across multiple columns you have to insure that only one of the OR conditions can be true before a UNION ALL will work.


Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

The Dixie Flatline
The Dixie Flatline
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34521 Visits: 6944
Can you try IN, instead of OR... that can sometimes produce two index seeks against one table instead of a scan.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Asim Yousaf
Asim Yousaf
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5491 Visits: 177
Break OR Sign with UNION ALL can overcome performance and help results in required dataset
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search