Home > Uncategorized > Conditional INNER Joins

Conditional INNER Joins

Recently on  a newsgroup I found a question about conditional joins in TSQL. The OP was trying to get rid of dynamic SQL and was looking for a way to change the following example script

DECLARE @SQL VARCHAR(MAX) SET @SQL = 'SELECT * FROM T1' IF LEN(@Param1) > 0 SET @SQL = @SQL + ' INNER JOIN T2 ON T1.Col1 = T2.Col2' IF @Param2 > 0 SET @SQL = @SQL + ' WHERE T1.Col2 = @Param2' EXEC (@SQL)

What the OP was asking for was if it was possible to conditionally join to a table given a certain parameter value. What he was looking for was a way to do something like this

SELECT * FROM T1 IF LEN(@Param1) > 0 BEGIN INNER JOIN T2 ON T1.Col1 = T2.Col2 END IF @Param2 > 0 BEGIN WHERE T1.Col2 = @Param2 END

Now converting the second if statement is easy; just add a case statement to check the value of @Param2 and change the RHS of the equality operator based on value of @Param2

SELECT * FROM T1 IF LEN(@Param1) > 0 BEGIN INNER JOIN T2 ON T1.Col1 = T2.Col2 END WHERE T1.Col2 = CASE @Param2 WHEN 0 THEN T1.Col2 ELSE @Param2 END

Conditionally adding the INNER JOIN is very easy when building up a SQL string but the above would not work because of syntax errors. So how do we do this in pure TSQL? simple; we change our where clause to check for existence of matching rows in the second table T2 and OR with the parameter

SELECT * FROM T1 WHERE ( EXISTS( SELECT * FROM T2 WHERE T1.Col1 = T2.Col2 ) OR LEN(@Param1) = 0 ) AND T1.Col2 = CASE @Param2 WHEN 0 THEN T1.Col2 ELSE @Param2 END

The above generates an optimal query plan because SQL Server knows it needs to access T2 and it will appear in the plan but the LEN(@Param1) = 0 condition prevents it from being accessed when there is no need to.

An alternate approach would be to write separate queries and use IF statements to determine which query needs to be executed

IF LEN(@Param) > 0 BEGIN SELECT * FROM T1 INNER JOIN T2 ON T1.Col1 = T2.Col2 WHERE T1.Col2 = CASE @Param2 WHEN 0 THEN T1.Col2 ELSE @Param2 END END ELSE BEGIN SELECT * FROM T1 WHERE T1.Col2 = CASE @Param2 WHEN 0 THEN T1.Col2 ELSE @Param2 END END

But this can get pretty nasty if the number of parameters / conditions grows. An example would be a search SP which searches against Keyword, Product Category, Price, Inventory Quantity etc. Imagine the number of separate queries and the nightmare to maintain / modify them if needed. The conditional inner join version has the benefit of being a single query which can be easily modified if need be. But do experiment and perhaps a hybrid solution would be the best in your case where a couple of IF statements with separate queries each with some conditional inner joins are used.

 

Technorati Tags: ,

Advertisements
Tags:
  1. Alex
    November 24, 2010 at 6:20 am

    Here’s a two other alternatives:

    Alternative 1
    ==================
    SELECT *
    FROM T1
    WHERE
    (
    (LEN(@Param1) = 0) OR
    (LEN(@Param1) > 0 and EXISTS(SELECT * FROM T2 WHERE T1.Col1 = T2.Col2)
    )
    AND (
    (@Param2 = 0) OR
    (@Param2 > 0 AND T1.Col2 = @Param2)
    )

    Alternative 2
    ==================
    SELECT *
    FROM T1
    WHERE
    (
    (LEN(@Param1) = 0) OR
    (LEN(@Param1) > 0 and Col1 IN (SELECT Col2 FROM T2)
    )
    AND (
    (@Param2 = 0) OR
    (@Param2 > 0 AND T1.Col2 = @Param2)
    )

  2. Pankaj
    June 12, 2013 at 7:18 am

    Alex – Thanks alot! Exactly what i was looking after 🙂

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: