Connection of tables inside the brackets. Role and benefits

[obi]

I've been in touch with a stranger who can describe a simple example:

WITH A AS 
(
  SELECT 1 A, 6 B FROM DUAL UNION ALL
  SELECT 2 A, 5 B FROM DUAL UNION ALL
  SELECT 3 A, 4 B FROM DUAL UNION ALL
  SELECT 4 A, 3 B FROM DUAL UNION ALL
  SELECT 5 A, null B FROM DUAL UNION ALL
  SELECT 6 A, 1 B FROM DUAL 
)
SELECT '||A1||', A1.*,
       '||A2||', A2.*,
       '||A3||', A3.*
  FROM A A1
  JOIN (A A2
        LEFT JOIN A A3 ON A2.A=A3.B)
    ON A1.A = A3.B;

This code is fully operational in Oracle 12c, but it doesn't work, for example, in an impala (without DUAL, of course).

The point is, this is the first time I see JOIN inside the bracket. However, these brackets do not receive their pseudonym and are attached to other tables with reference to one of the tables inside the brackets.

I'm trying to figure out why this is done and what benefits it is. Is this a forced optimist to do first? JOIN Tables А2 and А3? What benefits are JOIN Can you have a traditional approach?

WITH A AS (
  SELECT 1 A, 6 B FROM DUAL UNION ALL
  SELECT 2 A, 5 B FROM DUAL UNION ALL
  SELECT 3 A, 4 B FROM DUAL UNION ALL
  SELECT 4 A, 3 B FROM DUAL UNION ALL
  SELECT 5 A, null B FROM DUAL UNION ALL
  SELECT 6 A, 1 B FROM DUAL 
)
SELECT '||A1||', A1.*,
       '||A2||', A2.*,
       '||A3||', A3.*
  FROM A A1
  JOIN A A3 ON A1.A = A3.B
  LEFT JOIN A A2 ON A2.A=A3.B;

[Analeea]

The role of the brackets in three or more tables is the same as in any composition to change the order of implementation. ♪ https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__CHDIJFDJ This is stated as follows:

When you join more than two row sources, you can use parentheses to override default precedence. For example, the following syntax:

   SELECT ... FROM a JOIN (b JOIN c) ...

results in a join of b and c, and then a join of that result set with a♪

Default order is right on the left. The optimist may, however, modify this order on the basis of the conditions of the connection, the presence of indices and mn.outer jointhe order will remain as the result will depend on the order of implementation.

For a better understanding, the reproduced example of the way in which the connections play a role, it changes with the conclusion of one of the brackets:

with t (id, val) as (
    select rownum, column_value 
    from sys.odciVarchar2List ('a', 'b', 'c')
)
select a.id, c.val val  
from t a
left join t b on b.id=a.id and b.val != 'c'
     join t c on c.id=b.id
union all
select null, '#1 ^^^; #2 vvv' from dual
union all
select a.id, c.val val  
from t a
left join (
    t b join t c on c.id=b.id
    ) on b.id=a.id and b.val != 'c';
    ID VAL             


     1 a               
     2 b               
       #1 ^^^; #2 vvv  
     1 a               
     2 b               
     3