JOIN连接允许表别名重复

功能描述

Vastbase G100在Oracle兼容模式下，支持使用JOIN连接从多个表中读取数据时，在JOIN条件两端使用重复的表别名。

例如，在下面这个查询语句中，表别名“o”被重复使用：

select o.c1 from table1 o inner join table2 o on o.a = o.b;

完整示例请参考示例1。

JOIN表达式的判定规则

如果表别名的字段在两个表中都存在，数据库会根据从后向前的规则确定别名对应的表，例如：

select o.b2 from alias_t1 o inner join alias_t2 o on o.id = o.a;

对于o.id的表o，从后向前，首先考虑alias_t2表，alias_t2表存在id字段，因此o.id被识别为alias_t2.id; o.a在判断时的逻辑同理，首先考虑alias_t2表，而alias_t2表没有a字段，则下一步考虑alias_t1表，alias_t1表包含a字段，因此o.a被识别为alias_t1.a。

假设join条件的左右两端别名一致，字段一致，例如:

select o.b2 from alias_tl o inner join alias_t2 o on o.id = o.id;

那么根据从后向前的规则，被解释为alias_t2.id = alias_t2.id，因此alias_t1表并没有参与到JOIN的判断中，因此实际使用时不推荐这样写，虽然执行语句时数据库不会报错，但可能会出现期望之外的结果。

同理:

select o.b2 from alias_t1 o inner join alias_t2 o on o.id = o.b;

上述语句中o.id会被判断成alias_t2.id，o.b会被判断成alias_t2.b，表alias_t1也没有参与到JOIN判断中。

注意事项

该功能仅在数据库兼容模式为Oracle时能够使用（即创建DB时DBCOMPATIBILITY='A'），在其他数据库兼容模式下不能使用该特性。
JOIN左右使用相同表别名的原表可以来自不同的schema。
从后向前的规则只适用于INNER JOIN、LEFT JOIN、RIGHT JOIN。在FULL JOIN中，不能出现表达式的字段为两表共有的情况（即表达式左右的字段必须是A表有而B表没有，或是B表有而A表没有的），考虑如下SQL语句:
```
select o.b2 from alias_t1 o full join alias_t2 o on o.id= o.id;
```
在上述语句中，由于alias_t1和alias_t2都含有id字段，因此这条SQL执行时将会报错。

对于SELECT后的字段，此规则同样适用：不能出现表达式的字段在两表中都存在的情况。考虑如下几条SQL语句：
```
select o.id from alias_t1 o inner join alias_t2 o on o.a= o.b;
select o.* from alias_t1 o inner join alias_t2 o on o.a= o.b;
```
select后的字段为o.id，而alias_t1和alias_t2都含有id字段，无法得知o.id的o到底指代的是alias_t1表还是alias_t2表，因此将会报错处理。

对于select后的字段o.*，更加无法确定o指代的是哪张表，也会报错处理。

综上，一旦使用了重复的表别名，假设重复的别名为o，那么SQL中出现o.*，schema.o.*，catalog.schema.o.*均会报错。

示例

前置条件

创建并切换至兼容模式为Oracle的数据库下。

CREATE DATABASE db_oracle dbcompatibility='A';
\c db_oracle

示例1： 查询时用INNER JOIN连接两张表并使用相同的表别名。

1、创建两张测试表并插入数据。

create table alias_t1(id int,a int,a2 int);
create table alias_t2(id int,b int,b2 int);
insert into alias_t1 values(1,1,10);
insert into alias_t2 values(1,1,20);
insert into alias_t2 values(5,5,21);

2、使用INNER JOIN进行连接查询，表别名“o”被INNER JOIN左右的两张表重复使用。

select o.a2 from alias_t1 o inner join alias_t2 o on o.a = o.b ;

返回结果如下：

 a2
----
 10
(1 row)

示例2： 在WHERE子句中使用JOIN，并给JOIN左右的表使用相同的表别名。

1、分别创建两张测试表并插入数据。

CREATE TABLE tb_emp (
id int NOT NULL ,
name varchar(200) ,
age int ,
job varchar(200) ,
salary int ,
entrydate date ,
managerid int ,
dept_id int ,
PRIMARY KEY (id)
) ;
INSERT INTO tb_emp VALUES (1, 'jack', 20, '项目经理', 12500, '2005-12-01', 1, 1);
INSERT INTO tb_emp VALUES (2, 'tom', 60, '会计', 48000, '2006-06-01', 7, 3);
INSERT INTO tb_emp VALUES (3, 'lily', 23, '市场总监', 14000, '2009-06-01', 1, 2);
INSERT INTO tb_emp VALUES (4, 'nancy', 56, '职员', 3750, '2009-06-01', 10, 2);
INSERT INTO tb_emp VALUES (5, 'leo', 88, '销售总监', 14000, '2004-06-01', 1, 4);
INSERT INTO tb_emp VALUES (6, '张三', 38, '销售', 4600, '2009-06-01', 14, 4);
INSERT INTO tb_emp VALUES (7, '李明', 42, NULL, 2000, '2022-04-25', 1, NULL);

CREATE TABLE tb_dept (
id int NOT NULL ,
name varchar(200) ,
PRIMARY KEY (id)
) ;
INSERT INTO tb_dept VALUES (1, '研发部');
INSERT INTO tb_dept VALUES (2, '市场部');
INSERT INTO tb_dept VALUES (3, '财务部');
INSERT INTO tb_dept VALUES (4, '销售部');

2、对两张表进行连接查询，并返回财务部的最高薪水。

select max(salary) from tb_emp e join tb_dept e on e.dept_id=e.id where e.dept_id=3;

返回结果如下：

  max
-------
 48000
(1 row)

VastbaseG100

JOIN连接允许表别名重复

功能描述

注意事项

示例

弹框头部