Dynamic/Conditional SQL Join?

Question

I have data in a MSSQL table (TableB) where [dbo].tableB.myColumn changes format after a certain date...

I'm doing a simple Join to that table..

Select [dbo].tableB.theColumnINeed from [dbo].tableA 
left outer join [dbo].tableB on [dbo].tableA.myColumn = [dbo].tableB.myColumn

However, I need to join, using different formatting, based on a date column in Table A ([dbo].tableA.myDateColumn).

Something like...

Select [dbo].tableB.theColumnINeed from [dbo].tableA 
left outer join [dbo].tableB on [dbo].tableA.myColumn = 
    IF [dbo].tableA.myDateColumn > '1/1/2009'
        BEGIN
            FormatColumnOneWay([dbo].tableB.myColumn)
        END
    ELSE
        BEGIN
            FormatColumnAnotherWay([dbo].tableB.myColumn)
        END

I'm wondering if there's a way to do this.. or a better way I'm not thinking of to approach this..

Answer 1

SELECT [dbo].tableB.theColumnINeed
FROM   [dbo].tableA 
LEFT OUTER JOIN [dbo].tableB
ON [dbo].tableA.myColumn = 
   CASE
    WHEN [dbo].tableA.myDateColumn <= '1/1/2009' THEN FormatColumnOneWay([dbo].tableB.myColumn)
    ELSE FormatColumnAnotherWay([dbo].tableB.myColumn)
   END

Answer 2

Rather than having a CASE statement in the JOIN, which will prevent the query using indexes, you could consider using a UNION

SELECT [dbo].tableB.theColumnINeed 
FROM   [dbo].tableA 
    LEFT OUTER JOIN [dbo].tableB 
         ON [dbo].tableA.myDateColumn > '1/1/2009'
        AND [dbo].tableA.myColumn = FormatColumnOneWay([dbo].tableB.myColumn)
UNION ALL
SELECT [dbo].tableB.theColumnINeed 
FROM   [dbo].tableA 
    LEFT OUTER JOIN [dbo].tableB 
         ON [dbo].tableA.myDateColumn <= '1/1/2009'
        AND [dbo].tableA.myColumn = FormatColumnAnotherWay([dbo].tableB.myColumn)

but if the FormatColumnOneWay / FormatColumnAnotherWay are functions, or field expressions, that is probably going to exclude use of inxdexes on [myColumn], although any index on myDateColumn should still be used

However, it might help to understand what the FormatColumnOneWay / FormatColumnAnotherWay logic is, as knowning that may enable a better optimisation

Couple of things to note:

UNION ALL will not remove any duplicates (unlike UNION). Because the two sub-queries are mutually exclusive this is OK and saves the SORT step which UNION would make to enable it to remove duplicates.

You should not use '1/1/2009' style for string-dates, you should use 'yyyymmdd' style without and slashes or hyphens (you can also use CONVERT with an parameter to explicitly indicate that the string is in d/m/y or m/d/y style