SQL Conditional JOIN (inner or outer)

I'd like to know if it's possible to do a conditional statement when choosing which join to do. A example of what I mean might be something like:

SELECT * FROM Table1
IF (TRUE) THEN INNER JOIN
ELSE (FALSE) THEN OUTER JOIN
END
Table2 ON (SOME CONDITION)

This obviously doesn't work, but I think it i开发者_开发技巧llustrates my question better.

This assumes you'd have the same columns for both inner and outer

An outer join includes an inner join of course: the only difference is how non-matching rows are handled which can be sorted in the WHERE clause

SELECT
   col1, col2
FROM
   Table1
   LEFT OUTER JOIN
   Table2 ON ... (SOME CONDITION)
WHERE
   (conditional = true AND table2.key IS NOT NULL)
   OR
   (conditional = false AND table2.key IS NULL)

No guarantees about performance with an OR

No, but what you can do is create both joins, and then use a conditional to select which one you extract data from for some output column...

Select Case When [Some boolean condition]
        Then A.ColumnName
        Else B.ColumnName End as OutputColumnName
   From Table 
     Left Join OneTable As A
        On [Join conditions]
     Left Join OtherTable As B
        On [Join conditions]

This can be modified to address your specific concern [Inner, Outer], in the same way

  Select Case When [Some boolean condition]
        Then A.ColumnName
        Else B.ColumnName End as OutputColumnName
   From Table 
     [Inner] Join TheTable As A
        On [Join conditions]
     Left [Outer] Join TheTable As B
        On [Join conditions]

It's not at all clear what you expect to get back. Do you expect the IF to work on a row-by-row basis, or once for the whole query?

In any event, you can probably get what you want with the OUTER JOIN, and then filtering the results with a WHERE clause.

No. If this is necessary you need to dynamically build the query string and then execute it. Or just always do the outer join and select which columns to actually use in your front end based on the condition. It's hard to say which makes more sense in any given case without more info.