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.