title: I Need some sort of Conditional Join
body: <p>Okay, I know there are a few posts that discuss this, but my problem cannot be solved by a conditional where statement on a join (the common solution).</p>
  
  <p>I have three <code>join</code> statements, and depending on the query parameters, I may need to run any combination of the three. My Join statement is quite expensive, so I want to only do the join when the query needs it, and I'm not prepared to write a 7 combination <code>IF..ELSE..</code> statement to fulfill those combinations.</p>
  
  <p>Here is what I've used for solutions thus far, but all of these have been less than ideal:</p>
  
  <pre><code>LEFT JOIN joinedTable jt
  ON jt.someCol = someCol
  WHERE jt.someCol = conditions
  OR @neededJoin is null
  </code></pre>
  
  <p>(This is just too expensive, because I'm performing the join even when I don't need it, just not evaluating the join)</p>
  
  <pre><code>OUTER APPLY
  (SELECT TOP(1) * FROM joinedTable jt 
  WHERE jt.someCol = someCol
  AND @neededjoin is null)
  </code></pre>
  
  <p>(this is even more expensive than always left joining)  </p>
  
  <pre><code>SELECT @sql = @sql + ' INNER JOIN joinedTable jt ' +
               ' ON jt.someCol = someCol ' +
               ' WHERE (conditions...) '
  </code></pre>
  
  <p>(this one is IDEAL, and how it is written now, but I'm trying to convert it away from dynamic SQL).</p>
  
  <p>Any thoughts or help would be great!</p>
  
  <p><strong>EDIT:</strong>
  If I take the dynamic SQL approach, I'm trying to figure out what would be most efficient with regards to structuring my query. Given that I have three optional conditions, and I need the results from all of them my current query does something like this:</p>
  
  <pre><code>IF condition one
  SELECT from db
  INNER JOIN condition one
  
  UNION
  
  IF condition two
  SELECT from db
  INNER JOIN condition two
  
  UNION
  
  IF condition three
  SELECT from db
  INNER JOIN condition three
  </code></pre>
  
  <p>My non-dynamic query does this task by performing left joins:</p>
  
  <pre><code>SELECT from db
  LEFT JOIN condition one
  LEFT JOIN condition two
  LEFT JOIN condition three
  WHERE condition one is true
  OR condition two is true
  OR condition three is true
  </code></pre>
  
  <p>Which makes more sense to do? since all of the code from the "SELECT from db" statement is the same? It appears that the union condition is more efficient, but my query is VERY long because of it....</p>
  
  <p>Thanks!</p>