What is this "nested JOIN" syntax without a subquery called?

Question

In SQL Server (T-SQL), I can JOIN to a subquery of course, but I just learned I can also JOIN to a nested JOIN expression that isn't a subquery. For example:

SELECT *
FROM dbo.Customer c
JOIN ( /* note no SELECT */
    dbo.Table2 t2 
    JOIN dbo.Table3 t3 ON t3.t2Id = t2.Id
) ON c.t2Id = t2.Id  /* note no alias */
WHERE c.CustomerId = 1234;

What is this "nested JOIN without subquery" syntax called and where can I find it in the Microsoft documentation? It isn't mentioned in the context of joins or of subqueries.

Answer 1

Not a derived table. Not a subquery. Not a temp table. What it does is logically defines the order that the joins are performed. It has little utility with inner joins. Where it really comes in useful is when used with outer joins.

An example use case might be "all orders left join to back-ordered products", but where there is a junction table in between. This can be coded as:

...
FROM Order O
LEFT JOIN (
    OrderDetail OD
    JOIN Product P
        ON P.ProductId= OD.ProductId
        AND P.IsBackOrdered = 1 -- Not proper design, but it's just an example
    )
    ON OD.OrderId = O.OrderId

This allows the order-detail and product rows to be combined and filtered before being fed to the LEFT JOIN.

The parenthesis are actually optional, but I prefer to include them to somewhat improve readability (giving the reader a hint that something tricky is going on).

But as to "what is it called?" I don't think it has a name. I think of it as grouped joins or out-of-order joins. It's almost like the Reverse Polish Notation (RPN) of HP calculators back in the days of the HP-35 and successors.