FROM 子句中的嵌套括号是否有效的 Oracle SQL 语法? [英] Are nested parentheses in the FROM clause valid Oracle SQL syntax?

问题描述

此查询是否使用正确的 Oracle 语法?

Does this query use correct Oracle syntax?

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

它适用于 11g 和 12c，但它真的有效吗?或者这只是一个编译器错误"，将来可能会修复，导致代码失败?

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. 除了添加额外的括号外，它似乎没有任何作用.像 ((1+2)*3) 这样的表达式显然可以从嵌套括号中受益，但我看不出它们如何帮助 FROM 子句.当我查看上面的查询时，别名a"看起来超出了范围.
2. 我在 SQL 语言参考语法中找不到此语法的有效路径图表.另一方面，很容易看出 表达式、条件、和子查询.表达式、条件和子查询是递归的，可以包含括号，但连接子句不是递归的.

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.

我担心这一点，因为有类似的情况，无效的语法在一个版本中有效，然后在下一个版本中失败.例如:select (select count(*) from (select * from scott.emp where ename = dual.dummy)) from dual;.该查询在 10.2.0.1.0 中工作但在更高版本中停止工作，因为 表引用仅限于一级深度.

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?

推荐答案

在FROM的join子句中使用括号是合法的语法，括号确实有效果.

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

考虑这个查询:

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.

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

如果没有括号，试图将其表达为左连接是不可能的.您要么不会从表 a 中获得第 11-30 行，否则表 c 的第 11-20 行将是 nulls(取决于你是怎么做到的).

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;

，不需要括号.所以如果你真的想避免在 FROM 子句中使用括号，你通常可以这样做.就个人而言，我更喜欢带括号的 LEFT JOIN 方法而不是 RIGHT JOIN.

, 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.

这篇关于FROM 子句中的嵌套括号是否有效的 Oracle SQL 语法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！