T-SQL - How to write a conditional join

Question

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

Answer 1

If I understand correctly it sounds like your join conditions would be the equivalent of
ON ((@AddressID IS NOT NULL) AND (alias.column = @AddressID)) and likewise for the group join.

I use this conditional join at times.

Answer 2

Yes, it's very simple. Do left joins on the address and groups. Then in the where clause...

(@group_id is null or g.group_id = @group_id)
and (@address_id is null or a.address_id = @address_id)

Answer 3

You should be able to expand on this...

DECLARE @SQL varchar(max)

    SET @SQL = 'SELECT * FROM PERSON P'

    IF NULLIF(@ADDRESSID,"") IS NULL SET @SQL = @SQL + " INNER JOIN ADDRESSES A ON P.AddressID = A.AddressID"

    EXEC sp_executesql @SQL, N'@ADDRESSID int', @ADDRESSID

Answer 4

What Quntin posted is nice however there are some performance issues with it. Believe it or not what is faster is to check each parameter and write the SQL Join based on the case

In addition:

IF @AddressParameter IS NOT NULL
BEGIN
SELECT blah1, blah2 FROM OneTable INNER JOIN AddressTable WHERE ....
-more code
END
ELSE...
BEGIN
END
...

Another thing you can do is perform the joins and in the query filter (the where clause) you can do:

WHERE
(Address = @Address OR @Address IS NULL)

Performance here is shady as well.

Answer 5

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 ;)

Answer 6

Join the three tables together and use something like this in your WHERE clause:

WHERE Addresses.ID = COALESCE(@AddressID, Addresses.ID)
AND  Groups.ID = COALESCE(@GroupID, Groups.ID)