Cascade of outer joins

As a开发者_JAVA技巧 schematic example, I have 3 tables that I desire to join, A,B,C where A to B is joined via an outer join and B to C is potentially joined via an inner join. In this constellation, I have to write two outer joins to get data if the first join does not have a match A-B in a line:

SELECT [fields] FROM
       A
       LEFT OUTER JOIN
       B ON [a.field]=[b.field] 
       LEFT OUTER JOIN
       C ON [b.field]=[c.field]

It seems to me logically that I have to write the second statement as an outer join. However I'm curious if there is a possiblity to set brackets for the join scope to signal that the second join should only used if the first inner join has found matching data for A-B. Something like:

SELECT [fields] FROM
       A
      (LEFT OUTER JOIN
       B ON [a.field]=[b.field] 
       INNER JOIN
       C ON [b.field]=[c.field]
       )

I have played around a little but not found a possiblity to set brackets. The only way I have found to make this working is with a sub-query. Is this the only way to go?

Actually there is a syntax for that case.

SELECT fields
FROM A
  LEFT OUTER JOIN (
    B INNER JOIN C ON b.field = c.field
  ) ON a.field = b.field

The parentheses are optional, and the result is the same without them, being equivalent to the result of

SELECT fields
FROM A
  LEFT OUTER JOIN B ON a.field = b.field
  LEFT OUTER JOIN C ON b.field = c.field

You could perform it as follows

SELECT Fields 
FROM TableA a
LEFT OUTER JOIN (SELECT Fields
                 FROM TableB b
                 INNER JOIN TableC c ON b.Field = c.Field) x on a.Field = x.Fi

eld

Not too sure if there would be any performance benefit to this without testing it out though.

The 2nd way would be with a subquery as per Jon Bridges' answer

However, they are the same semantically.

A CTE could be used if you have a complex subquery

;WITH BjoinC AS
(
    SELECT Fields
    FROM TableB b
    INNER JOIN
    TableC c ON b.Field = c.Field
)
SELECT [fields] FROM
       A
       LEFT OUTER JOIN
       BjoinC ON ...

What About something like:

SELECT [fields] 
FROM A 
LEFT JOIN ( SELECT DISTINCT [fields]
            FROM B
            LEFT JOIN C ON b.field = c.field
          ) on a.field = b.field