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

Question

Does this query use correct Oracle syntax?

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

It works in 11g and 12c but is it truly valid syntax? Or is this is just a compiler "mistake" that might be fixed in the future, causing the code the fail?

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

1. It doesn't seem to do anything other than add extra parentheses. Expressions like ((1+2)*3) can obviously benefit from nested parentheses but I don't see how they would ever help the FROM clause. And when I look at the above query the alias "a" looks out of scope.
2. 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 permitted for expressions, conditions, and subqueries. Expressions, conditions, and subqueries are recursive and can contain parentheses, but a join clause is not recursive.

I worry about this because there have been similar cases where invalid syntax worked in one release and then failed in the next. For example: select (select count(*) from (select * from scott.emp where ename = dual.dummy)) from dual;. That query worked in 10.2.0.1.0 but stopped working in later versions because table references are scoped to only one level deep.

The original query has a bad style but it's not worth changing our production queries unless there is a real problem with it.

Is the query invalid? Or is there some legitimate reason for that syntax, or is there some path in the syntax diagrams I'm missing?

Answer 1

It is legal syntax to use parenthesis in a join clause in a FROM, and the parentheses do have an effect.

Consider this query:

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;

The parenthesis allow you to do an inner join of tables b and c and then outer join that to a.

Without the parenthesis, trying to express that as a left join would be impossible. You either wouldn't get rows 11-30 from table a or else rows 11-20 of table c would be nulls (depending on how you tried to do it).

Note that the above query is equivalent to:

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;

, which doesn't require parenthesis. So if you really want to avoid using parentheses in the FROM clause, you usually can do so. Personally, I prefer the LEFT JOIN method with parentheses instead of a RIGHT JOIN.

Answer 2

According to my reading of the syntax diagram for a SELECT statement, putting parentheses around a table reference in a SELECT statement is not allowed. As to whether or not Oracle might "fix" things in a way that would make this invalid, I have no way of knowing but I consider it unlikely. YMMV.

Best of luck.

EDIT

Just for fun I thought I'd put down my reading of the syntax diagram:

As other have noted, parentheses are allowed around a join_clause, but dual a is not a join_clause. Rather, it is a query_table_expression which is part of a table_reference. dual a cannot be a join_clause - to be such it would have to be followed by an inner_join_clause (e.g. INNER JOIN) or an outer_join_clause (e.g. LEFT OUTER JOIN, RIGHT OUTER JOIN or FULL OUTER JOIN), which it is not. Per the syntax diagram parentheses are not allowed around a query_table_expression unless the query_table_expression is preceded by ONLY, and in OP's query dual a is not preceded by ONLY. Thus I conclude that per the Oracle syntax diagrams ( ( (dual a) ) ) is not syntactically correct; however, the database seems to disagree. :-)