March 31, 2010 at 5:01 am
I have a SP with input parameter @Flag. Like to JOIN a table only when @Flag = True
I like to implement something like this.
SELECT *
FROM Table A
INNER JOIN
Case @Flag
WHEN TRUE
THEN Table B
END
ON ......
I know I could do it alternatively by some IF condition in top level .. but due to some other reason I don't like to do it..
I have tried with something below ..
SELECT *
FROM Table A
INNER JOIN Table B
ON ......
AND @Flag = True
It worked fine but from performance point of view what I understand here JOIN will happen first then Where clause will filter the data. Execution Plan also shows operations related to Table B. Although I can see @Flag = False is executing faster than @Flag=True. Can some one guide me for this case JOIN will at all happen or not ? How good is this code from performance point of view? Is there is any better way out to implement these ..
March 31, 2010 at 5:08 am
You can do it but the performance is often really poor. Instead, LEFT JOIN both tables, and use conditional processing of the output (in the SELECT) to return the data you want.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 31, 2010 at 5:12 am
You can simply do it using your flag variable
Example
If @strFlag = 'Not Join'
BEGIN
Select * from FirstTable
END
If @strFlag = Join'
BEGIN
Select * from FirstTable
Left Join SecondTable
On a.column = b.column
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply