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

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.

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.

> where can I find it in the Microsoft documentation?

In the FROM clause (as pointed out by @mustaccio in this cross site dupe).

Your FROM clause is

FROM   dbo.Customer c
       JOIN ( dbo.Table2 t2
              JOIN dbo.Table3 t3
                ON t3.t2Id = t2.Id )
         ON c.t2Id = t2.Id 

Note the grammar for joined_table is

<joined_table> ::=
{
    <table_source> <join_type> <table_source> ON <search_condition>
    ....
    | [ ( ] <joined_table> [ ) ]
}

So the following is a valid <joined_table>

 dbo.Table2 t2
  JOIN dbo.Table3 t3
    ON t3.t2Id = t2.Id 

The above grammar also shows the optional parentheses that can be applied to a <joined_table> to produce another valid <joined_table> so this is also one

( dbo.Table2 t2
  JOIN dbo.Table3 t3
    ON t3.t2Id = t2.Id )

And also as shown further up in the grammar on that documentation page <joined_table> is also a valid <table_source>.

So you are just doing

SELECT *
FROM   dbo.Customer c
       JOIN <joined_table>
         ON c.t2Id = t2.Id 

and, somewhat recursively, using a <joined_table> as a <table_source> to produce a further <joined_table>.

The parentheses are a table expression, and they mean you definitely want the server to complete that nested JOIN expression before the outer JOIN expression. For this query, where everything is an inner join, it's no different than listing the JOINs in the traditional way (just be careful of how you write the ON clauses):

SELECT *
FROM dbo.Customer c
JOIN dbo.Table2 t2 ON c.t2Id = t2.Id
JOIN dbo.Table3 t3 on t3.t2Id = t2.Id
WHERE c.CustomerId = 1234;

The above query will give the same results, and is arguably much easier to read and maintain.

However, it's possible to use an OUTER JOIN inside the parenthetical expression to build the results in ways that are easier to write and understand than a query without parentheses.