Dynamically choosing inner or outer join

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Dynamically choosing inner or outer join

Dynamically choosing inner or outer join

Rate Topic

Display Mode

Topic Options

Author

Message

jcb

Posted Monday, June 16, 2008 7:24 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 2:15 PM
Points: 1,176, Visits: 674

Hi,

Today a developer bring me a problem.
He need to rewrite a SP and add some parameters to filter the results.

The SP is like:

[SP_OLD] @param1
as
select tableA ta
left join tableB tb on tb.idA=ta.idA
left join tableC tc on tc.idB=tb.idB
where
ta.param1 = @param1

and now needs to evaluate like:

[SP_NEW] @param1, @param2
as

set @innerjoin = dosomethingwith(param1,param2)

if @innerjoin = false

select *
from tableA ta
left join tableB tb on tb.idA=ta.idA
left join tableC tc on tc.idB=tb.idB
where
ta.param1 = @param1
and ta.param2 = @param2

else

select *
from tableA ta
inner join tableB tb on tb.idA=ta.idA
inner join tableC tc on tc.idB=tb.idB
where
ta.param1 = @param1

I guess the better way do do it is create TWO SPs and let the app chose one or another...but the developer don't want to change some VB6 dlls (he got enough dll hell from this project).

I managed to do it using IF..ELSE but i wonder if exists a better way. Can i use CASE in the JOIN clause?

The query is pretty big and uses old fashioned joins in the WHERE clause (=, =* and *=).

I will use the better answer to rewrite lots of scripts in a old-big-bad BD designed app.

Thanks in advance.
Jean C. Bulinckx

Post #517535

Vladan

Posted Monday, June 16, 2008 8:41 AM

SSCommitted

Group: General Forum Members
Last Login: Thursday, May 05, 2011 1:38 AM
Points: 1,636, Visits: 604

You can't use CASE to determine whether the join will be LEFT JOIN or INNER JOIN if that's what you were asking.
You probably could use additional conditions in WHERE clause to achieve similar effect, but IMHO your solution with IF and two separate blocks of code is better. See the code below for how that could be done (the idea is tested and works, but you may need to modify it for your environment - you didn't explain much about the @innerjoin parameter, so I wrote it as if it was varchar with values "Yes" or "No").

SELECT *
FROM tableA ta
LEFT JOIN tableB tb ON tb.idA=ta.idA
LEFT JOIN tableC tc ON tc.idB=tb.idB
WHERE ta.param1 = @param1
    AND ta.param2 = @param2
    AND (tb.idA=ta.idA OR @innerjoin = 'No')
    AND (tc.idB=tb.idB OR @innerjoin = 'No')

Edit: To be correct, the way I wrote it, any other value for @innerjoin than 'No' will cause that the query will perform as inner joined.

Post #517599

GilaMonster

Posted Monday, June 16, 2008 8:49 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

jcb (6/16/2008)

I guess the better way do do it is create TWO SPs and let the app chose one or another...but the developer don't want to change some VB6 dlls (he got enough dll hell from this project).

Write 3 stored procs. The app always calls the one and passed the param to say left or inner join. Based on that the proc calls one of 2 other procedures. No change to the dll needed.

I must admit, I'm curious about what would need the join type changing at run time. Reports?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #517601

Michael Earl-395764

Posted Monday, June 16, 2008 11:35 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

You could always do a LEFT JOIN and just specify a filter based on your @innerjoin parameter

select *
from tableA ta
left join tableB tb on tb.idA=ta.idA
left join tableC tc on tc.idB=tb.idB
where
ta.param1 = @param1
and (@innerjoin = false or ta.param2 = @param2)
AND (@innerjoin = false or tb.idA is not null)
AND (@innerjoin = false or tc.idB is not null)

Post #517761

jcb

Posted Tuesday, June 17, 2008 12:30 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 2:15 PM
Points: 1,176, Visits: 674

Nice work guys!

Vladan,

yep, I wondered about a case in the where clause. Why not?

GilaMonster,

Great! I can keep the original SP and make 2 news SPs in that context.
It´s pretty good because the app is a monster and is hard to determine if are others calls to that SP over thousands of SPs, triggers, ASP, DLLs, jobs...

PS: This call is used to validate something in a long and complex chain of rules (from my view point its like one of Tom's trap to catch Jerry).

Michael Earl,

Nice and direct like the Vladan solution.
That was my first approach. Unfortunelly the real world query uses joins in the where clause and i got a run time error.
Just because u cannot join the same tables "twice" while the same tables are in a outer join. I tested u query and it worked. Maybe its a limitation only for "old fashioned" joins.

Thanks for all u time guys!

Post #518495

Vladan

Posted Wednesday, June 18, 2008 1:00 AM

SSCommitted

Group: General Forum Members
Last Login: Thursday, May 05, 2011 1:38 AM
Points: 1,636, Visits: 604

jcb,
this is one of my favorite links - it discusses how to apply various search criteria, and mentions many possible solutions, their advantages, drawbacks and performance. Maybe it will help you to choose the best way.
-> Dynamic Search Conditions in T-SQL

Many other interesting articles by the same author can be accessed from Erland Sommarskog's home page

Post #518784

« Prev Topic | Next Topic »

Permissions