Are nested parentheses in a FROM clause valid Oracle SQL syntax?

Question

Does this query use the correct Oracle syntax?

select * from ( ( ( dual a) ) ) where a.dummy = 'X';

It works in 11g and 12c, but is this really the syntax? Or is it just a “compiler error” that might be fixed in the future, causing code failure?

I doubt this is the correct syntax for the following reasons:

• It seems to be doing nothing but adding extra parentheses. Expressions like ((1 + 2) * 3) can obviously be useful from nested parentheses, but I don’t see how they would ever help in the FROM clause. And when I look at the above query, the alias "a" looks out of scope.
• I cannot find a valid path for this syntax in the SQL Language Reference syntax diagrams . On the other hand, it's easy to see how nested parentheses are allowed for expressions , conditions , and subqueries . Expressions, conditions, and subqueries are recursive and may contain parentheses, but the join clause is not recursive.

I am worried about this because there were times when one release executed invalid syntax and then did not execute the next. For example: select (select count(*) from (select * from scott.emp where ename = dual.dummy)) from dual;. This query worked in 10.2.0.1.0, but stopped working in later versions, because table links have only one depth level .

, , .

? - , - , ?

Answer 1

join FROM, .

:

WITH table_a AS ( SELECT rownum id FROM DUAL CONNECT BY LEVEL <= 30),
     table_b as ( SELECT rownum id FROM DUAL CONNECT BY LEVEL <= 20),
     table_c AS ( SELECT rownum id FROM DUAL CONNECT BY LEVEL <= 10)
SELECT a.id, b.id, c.id
FROM   table_a a left join ( table_b b inner join table_c c ON c.id = b.id ) ON b.id = a.id 
ORDER BY 1,2,3;

b c, , a.

, , . 11-30 a, 11-20 c null ( , ).

, :

WITH table_a AS ( SELECT rownum id FROM DUAL CONNECT BY LEVEL <= 30),
     table_b as ( SELECT rownum id FROM DUAL CONNECT BY LEVEL <= 20),
     table_c AS ( SELECT rownum id FROM DUAL CONNECT BY LEVEL <= 10)
SELECT a.id, b.id, c.id
FROM   table_b b inner join table_c c on c.id = b.id right join table_a a on a.id = b.id 
ORDER BY 1,2,3;

. , FROM, . LEFT JOIN RIGHT JOIN.

Answer 2

SELECT, SELECT . , Oracle "" , , , . YMMV.

.

, , :

, join_clause, dual a join_clause. , query_table_expression, table_reference. dual a join_clause - inner_join_clause (, INNER JOIN) outer_join_clause (, LEFT OUTER JOIN, RIGHT OUTER JOIN FULL OUTER JOIN), . query_table_expression, query_table_expression ONLY, OP- dual a ONLY. , , Oracle ( ( (dual a) ) ) ; , , .: -)

Answer 3

join_clause .

,

 (((select * from dual)));

.

 select * from (((dual)));

Starting with select , go to query_block

select --> subquery --> query_block

query_block expands to

SELECT * FROM table_reference

From table_referencewe leave to (nested) subquery, which can be further nested.

table_reference --> query_table_expression --> ( subquery )

So, keep expanding subqueryto get the required attachment and finally select query_table_expressionTABLE as the extension

But, as noted by MT0 and others, this, unfortunately, does not lead to the expected result. Maximum legal request

select * from (((select * from dual)));