Cascade of external associations

Question

As a schematic example, I have 3 tables that I want to join, A, B, C, where AB is connected via an external join, and BC is potentially connected through an internal join. In this constellation, I have to write two outer joins to get the data if the first connection does not have an AB match in the string:

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

It seems logical to me that I should write the second statement as an external join. However, I am curious if it is possible to set the brackets for the join area in order to signal that the second join should only be used if the first inner join has detected the corresponding data for AB. Sort of:

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

I played a little, but did not find the ability to set the brackets. The only way I found work was through a subquery. Is this the only way to go?

Answer 1

Actually there is syntax for this case.

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

The brackets are optional, and the result is left without them, which is equivalent to the result

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

Answer 2

You can execute 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 

field

Not too sure that there will be no performance benefits for this, but without testing it.

Answer 3

The second way would be with a subquery according to John Bridges answer

However, they are the same semantically.

A CTE can 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 ... 

Answer 4

Sort of:

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