Or conditional join

The case:

SELECT ...

FROM TableA a

JOIN TableB b ON (a.ID = b.ID OR a.ID = b.ParentID)

or:

JOIN TableB b ON     a.ID = b.ID

                           AND    (b.AColumn = @AColumn OR @AColumn = 0)

                           -- @AColumn = 0 mean no condition ("all")

Of course it's not forbidden to use conditional join, 

but most of the time it won't be good to the performance of the query.

There are few possible solutions.

0. Keep use the conditional join

For cases the the performance are not get Worse (example: small tables),

or when the 'complecated' cases are uncommon.

1. UNION

Good for 'simple' cases:

Instead of:

SELECT ...

FROM TableA a

JOIN TableB b ON (a.ID = b.ID OR a.ID = b.ParentID)

use:

SELECT ...

FROM TableA a

JOIN TableB b ON a.ID = b.ID

UNION

SELECT ...

FROM TableA a

JOIN TableB b ON a.ID = b.ParentID

or similar case:

Instead of:

SELECT ...

FROM TableA a

JOIN TableB b ON     a.ID = b.ID

                           AND    (b.AColumn = a.AColumn OR b.BColumn = a.BColumn)

Use:

       SELECT ...

       FROM TableA a

       JOIN TableB b ON     a.ID = b.ID

                                  AND b.AColumn = a.AColumn

UNION

SELECT ...

FROM TableA a

JOIN TableB b ON     a.ID = b.ID

                           AND    b.BColumn = a.BColumn

2. filter in the "where":

Instead of:

SELECT ...

FROM TableA a

JOIN TableB b ON     a.ID = b.ID

                                  AND    (b.AColumn = @AColumn OR @AColumn = 0)

SELECT ...

FROM TableA a

JOIN TableB b ON     a.ID = b.ID

WHERE (b.AColumn = @AColumn OR @AColumn = 0))

3. 2 queries:

For cases that the JOIN is depended on a parameter:

Instead of:

SELECT ...

FROM TableA a

JOIN TableB b ON     a.ID = b.ID

                           AND    (b.AColumn = @AColumn OR @AColumn = 0)

                           -- @AColumn = 0 mean no condition ("all")

Use:

IF @AColumn = 0

       SELECT ...

       FROM TableA a

       JOIN TableB b ON     a.ID = b.ID

ELSE

SELECT ...

FROM TableA a

JOIN TableB b ON     a.ID = b.ID

                           AND    b.AColumn = @AColumn -- without consider the case of @AColumn = 0

4. Insert data to temp table

It's good for similar cases like in point 2, but for queries with a lot of joined tables

What we'll do is to store the data for the conditional join in a temp table and use it in the main query:

Instead of:

SELECT ...

FROM TableA a

JOIN TableB b ON     a.ID = b.ID

                           AND    (b.AColumn = @AColumn OR @AColumn = 0)

                           -- @AColumn = 0 mean no condition ("all")

JOIN TableC c .....

JOIN TableD d .....

JOIN TableE e .....

Use:

CREATE TABLE #TempCond (

INSERT INTO #TempCond ()

       SELECT ... -- take the relevant columns

       FROM TableA a

       JOIN TableB b ON     a.ID = b.ID

                                  AND    (b.AColumn = @AColumn OR @AColumn = 0)

--Or, if required, use one of the solutions in points 1,2 to this temp insertion

SELECT ...

FROM #TempCond

JOIN TableC c .....

JOIN TableD d .....

JOIN TableE e .....