Menu
I have a stored procedure with a number of parameters. I would like to write my query so that it joins with certain tables but only if a particular parameter has a value. Take the following example: I have a Person table. There is also an Address table which holds Person Addresses and a Groups table that holds Person Groups. Both are one to many relationships with the Person table. My stored procedure has an @AddressID parameter and a @GroupID parameter.
The query always just returns fields from the Person table. If neither parameter has a value then the query should return all records from the Person table. If the @AddressID parameter is supplied then it should return only records that have a matching record in the Address table and ignore the Groups table. If the @GroupID parameter is supplied then it should return only records that have a matching record in the Groups table and ignore the Addresses table. If both parameters are supplied then it should only show records that have a matching record in both tables. Make sense?
Is there a simple way to do this that I am missing?
Thanks, Corey
307
A conditional column join is a fancy way to let us join to a single column and to two (or more) columns in a single query. We can accomplish this by using a case statement in the on clause of our join. A case statement allows us to test multiple conditions (like an if/else if/else) to produce a single value.
To use the WHERE clause to perform the same join as you perform using the INNER JOIN syntax, enter both the join condition and the additional selection condition in the WHERE clause. The tables to be joined are listed in the FROM clause, separated by commas. This query returns the same output as the previous example.
The SQL CASE ExpressionThe CASE expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
putting conditions in join improve performance for large amount of data with properly indexed tables. @Shahdat if you are getting that significant a performance difference moving your filter conditions from the where clause to the inner join you need to post those execution plans.
If I understand correctly it sounds like your join conditions would be the equivalent ofON ((@AddressID IS NOT NULL) AND (alias.column = @AddressID)) and likewise for the group join.
I use this conditional join at times.
60
The simple ways are actualy not good solutions. As bad as it sounds, the best solution is to have explicit IF in the code and separate queries:
IF (condition) SELECT ... FROM Person WHERE ... ELSE IF (otherCondition) SELECT ... FROM Person JOIN ... ON ... WHERE ... ELSE IF (moreCondition) SELECT ... FROM Persons JOIN ... JOIN ... WHERE ... The reason for this is that if you're trying to build one single query that matches all three (or more) conditions then the engine has to produce one single query plan that works in all conditions. In T-SQL one statement equals one plan. Remember that plans are created for the generic case, for any variable value, so the result is always a very, very bad plan.
While is is counterintuitive and seems like a horrible solution to any programmer, this is how databases work. The reason why this is not a problem 99.99% of the times is that after trying what you ask and seeing what it has to be done, developers quickly come to their senses and revise their requirements so that they never have to run queries that optionaly join based on runtime variable values ;)
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
