T-SQL - How to write a conditional join

问题:

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

回答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.

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

回答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)

回答4:

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

回答5:

This is how I had done for my case.

DECLARE
    @ColorParam varchar(500)

SET
    @ColorParam = 'red, green, blue'

declare @Colors table
(
    Color NVARCHAR(50) PRIMARY KEY
)

-- populate @Colors table by parsing the input param, 
-- table can be empty if there is nothing to parse, i.e.: no condition
INSERT @Colors SELECT Value FROM dbo.Splitter(@ColorParam, ',')

SELECT
    m.Col1,
    c.Color
FROM
    MainTable AS m
FULL JOIN -- instead of using CROSS JOIN which won't work if @Colors is empty
    @Colors AS c
ON
    1 = 1 -- the trick
WHERE
    (@ColorParam IS NULL OR c.Color = m.Color)
    

回答6:

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.

回答7:

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)

回答8:

Uh, probably all of you have resolved this so far.

As i understand You, you want to have 'dynamic' query, to join table if parameter exists, or to omit join if parameter is null. Secret is in using left outer join. Like:

SELECT p.*
FROM Parent AS p
LEFT OUTER JOIN Child AS c ON p.Id = c.ParentId
WHERE
        (@ConditionId IS NULL OR c.ConditionId = @ConditionId)

How this works?

• If filter parameter @ConditionId is null, then there's no child for outer join, and result will have all Parent's.
• If filter parameter @ConditionId is not null, then outer join will join Child's with this parent, and condition (@ConditionId IS NULL OR c.ConditionId = @ConditionId) will throw out Parent's which didn't have joined Child's with condition c.ConditionId = @ConditionId.

LEFT OUTER JOIN for sure have performance issue, but as much as this works fast, i don't want to concatenate string's to create query.

回答9:

Left join and where clause should do the trick:

SELECT Customers.CustomerName, Customers.Country, Orders.OrderID
    FROM Customers
    LEFT JOIN Orders
    ON Customers.CustomerID=Orders.CustomerID
    WHERE Country= @MyOptionalCountryArg or @MyOptionalCountryArg is null;