sql - 在查询中两次使用相同的表别名

Question

我的同事是ANSI连接语法的新手，最近写了一个查询，如下所示:

SELECT count(*)
  FROM table1 t1
       JOIN table2 t2 ON
            (t1.col_a = t2.col_a)
       JOIN table3 t3 ON
            (t2.col_b = t3.col_b)
       JOIN table3 t3 ON
             (t3.col_c = t1.col_c);

请注意，table3在不同的列上同时连接到table1和table2，但是两个JOIN子句为table3使用相同的表别名。

查询运行，但是我不确定它的有效性。 这是编写此查询的有效方法吗？

我以为加入应该是这样的:

SELECT count(*)
  FROM table1 t1
       JOIN table2 t2 ON
            (t1.col_a = t2.col_a)
       JOIN table3 t3 ON
            (t2.col_b = t3.col_b AND
             t3.col_c = t1.col_c);

这两个版本在功能上是否相同？ 我的数据库中确实没有足够的数据可以确定。

谢谢。

Answer 1

第一个查询是4个表的联接，第二个查询是3个表的联接。因此，我不希望两个查询都返回相同数量的行。

SELECT *
  FROM table1 t1
       JOIN table2 t2 ON
            (t1.col_a = t2.col_a)
       JOIN table3 t3 ON
            (t2.col_b = t3.col_b)
       JOIN table3 t3 ON
             (t3.col_c = t1.col_c);
             

别名 t3仅在ON子句中使用。别名 t3引用ON关键字之前的表。我通过实验发现了这一点。所以以前的查询等价于

SELECT *
  FROM table1 t1
       JOIN table2 t2 ON
            (t1.col_a = t2.col_a)
       JOIN table3 t3 ON
            (t2.col_b = t3.col_b)
       JOIN table3 t4 ON
             (t4.col_c = t1.col_c);

可以在传统的连接中进行转换

SELECT *
  FROM table1 t1,
       table2 t2,
       table3 t3,
       table3 t4
where (t1.col_a = t2.col_a)
    and  (t2.col_b = t3.col_b)
    and (t4.col_c = t1.col_c);
    

第二个查询是

SELECT *
  FROM table1 t1
       JOIN table2 t2 ON
            (t1.col_a = t2.col_a)
       JOIN table3 t3 ON
            (t2.col_b = t3.col_b AND
             t3.col_c = t1.col_c);
             

这也可以转化为传统的联接

SELECT *
  FROM table1 t1,
    table2 t2,
    table3 t3
where (t1.col_a = t2.col_a)
    and (t2.col_b = t3.col_b)
    AND (t3.col_c = t1.col_c);
    

这些查询似乎有所不同。为了证明它们的区别，我们使用以下示例:

create table table1(
    col_a number,
    col_c number
);

create table table2(
    col_a number,
    col_b number
);

create table table3(
    col_b number,
    col_c number
);

insert into table1(col_a, col_c) values(1,3);
insert into table1(col_a, col_c) values(4,3);
insert into table2(col_a, col_b) values(1,2);
insert into table2(col_a, col_b) values(4,2);
insert into table3(col_b, col_c) values(2,3);
insert into table3(col_b, col_c) values(2,5);
insert into table3(col_b, col_c) values(7,9);

commit;

我们得到以下输出

SELECT *
  FROM table1 t1
       JOIN table2 t2 ON
            (t1.col_a = t2.col_a)
       JOIN table3 t3 ON
            (t2.col_b = t3.col_b)
       JOIN table3 t3 ON
             (t3.col_c = t1.col_c)


| COL_A | COL_C | COL_A | COL_B | COL_B | COL_C | COL_B | COL_C |
|-------|-------|-------|-------|-------|-------|-------|-------|
|     1 |     3 |     1 |     2 |     2 |     3 |     2 |     3 |
|     4 |     3 |     4 |     2 |     2 |     3 |     2 |     3 |
|     1 |     3 |     1 |     2 |     2 |     5 |     2 |     3 |
|     4 |     3 |     4 |     2 |     2 |     5 |     2 |     3 |



             
SELECT *
  FROM table1 t1
       JOIN table2 t2 ON
            (t1.col_a = t2.col_a)
       JOIN table3 t3 ON
            (t2.col_b = t3.col_b AND
             t3.col_c = t1.col_c)

| COL_A | COL_C | COL_A | COL_B | COL_B | COL_C |
|-------|-------|-------|-------|-------|-------|
|     4 |     3 |     4 |     2 |     2 |     3 |
|     1 |     3 |     1 |     2 |     2 |     3 |

检索的行数不同，因此 count(*)也不同。
别名的使用令人惊讶。至少对我来说。
以下查询有效，因为 t1中的 where_clause引用了 table2。

select *
from table1 t1 join table2 t1 on(1=1)
where t1.col_b<0;

以下查询有效，因为 t1中的 where_clause引用了 table1。

select *
from table1 t1 join table2 t1 on(1=1)
where t1.col_c<0;

以下查询引发错误，因为 table1和 table2都包含一列 col_a。

select *
from table1 t1 join table2 t1 on(1=1)
where t1.col_a<0;

引发的错误是

ORA-00918: column ambiguously defined

以下查询有效，别名 t1引用同一 where_clause中的两个不同的表。

select *
from table1 t1 join table2 t1 on(1=1)
where t1.col_b<0 and t1.col_c<0;

这些和更多示例可以在这里找到: http://sqlfiddle.com/#!4/84feb/12
最小的计数器示例
最小的计数器示例是

table1
col_a  col_c
    1      2

table2
col_a  col_b
    1      3

table3
col_b  col_c
    3      5
    6      2    

此处，第二个查询的结果集为空，第一个查询返回一行。可以证明，第二个查询的 count(*)从未超过第一个查询的 count(*)。
更详细的解释
如果我们详分割析以下语句，此行为将变得更加清楚。

SELECT t.col_b, t.col_c
  FROM table1 t
       JOIN table2 t ON
            (t.col_b = t.col_c) ;
        

这是从Oracle 12.2的 SQL Language Reference中的语法描述派生的Backus–Naur形式的此查询的简化语法。请注意，在每个语法图下都有一个指向该图的Backus–Naur形式的链接，例如 Description of the illustration select.eps。 “减少”是指我遗漏了所有未使用的可能性，例如 select定义为

select::=subquery [ for_update_clause ] ;

我们的查询未使用可选的 for_update_clause，因此我将规则简化为

select::=subquery

唯一的豁免是可选的 where-clause。我没有删除它，因此即使我们添加了 where_clause，该简化的规则也可以用于分析上述查询。
这些简化的规则将仅定义所有可能的select语句的子集。

select::=subquery 
subquery::=query_block
query_block::=SELECT select_list FROM join_clause [ where_clause ]
join_clause::=table_reference  inner_cross_join_clause ...  
table_reference::=query_table_expression  t_alias query_table_expression::=table
inner_cross_join_clause::=JOIN table_reference ON condition

所以我们的选择语句是 query_block，而 join_clause是类型

table_reference inner_cross_join_clause

其中 table_reference是 table1 t和 inner_cross_join_clause是 JOIN table2 t ON (t.col_b = t.col_c)。省略号 ...意味着可以有另外的inner_cross_join_clauses，但是我们在这里不需要。
在 inner_cross_join_clause中，别名 t指的是 table2。仅当无法满足这些引用时，才必须在外部范围内搜索别名。因此，ON条件中的以下所有表达式均有效:

t.col_b = t.col_c

这里的 t.col_b是 table2.col_b，因为 t指的是 inner_cross_join_clause的别名， t.col_c是 table1.col_c。 t的 inner_cross_join_clause(指 table2)没有列 col_c，因此将搜索外部范围并找到适当的别名。
如果我们有该条款

t.col_a = t.col_a

可以在此ON- inner_cross_join_clause所属的 condition中找到别名作为​​别名定义，以便将 t解析为 table2。
如果选择列表包含

t.col_c, t.col_b, t.col_a

而不是 *，然后将在 join_clause中搜索别名，并且 t.col_c将解析为 table1.col_c( table2不包含 col_c列)， t.col_b将解析为 table2.col_b( table1不包含 col_b)，但是 t.col_a将引发错误

ORA-00918: column ambiguously defined

因为对于select_list，任何aias定义都没有另一个定义。如果我们的查询中也包含 where_clause，则别名的解析方式与在 select_list中使用别名的方式相同。