T-SQL - How to write a conditional join

Solution 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.

Solution 2:

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

Solution 3:

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)