JOINS

允许合并多个relation的数据。

HetuEngine支持JOIN类型为：CROSS JOIN、INNER JOIN、OUTER JOIN（LEFT JOIN、RIGHT JOIN、FULL JOIN）、SEMIN JOIN和ANTI JOIN。

SELECT * FROM nation CROSS JOIN region;
SELECT * FROM nation, region;

SELECT * FROM nation (INNER) JOIN region ON nation.name=region.name;
SELECT * FROM nation ,region WHERE nation.name=region.name;

• 左外连接：LEFT JOIN或LEFT OUTER JOIN，表示以左表（nation）为基础返回左表所有的行及右表（region）中相匹配行的数据，若右表中没有匹配，则该行对应的右表的值为空。
• 右外连接：RIGHT JOIN或RIGHT OUTER JOIN，表示以右表（region）为基础返回右表所有的行及左表（nation）中相匹配行的数据，若左表中没有匹配，则该行对应的左表的值为空。
• 全外连接：FULL JOIN或FULL OUTER JOIN，表示只要其中某个表存在匹配，则返回相匹配的行，相当于LEFT JOIN和RIGHT JOIN结合。

SELECT * FROM nation LEFT (OUTER) JOIN region ON nation.name=region.name;
SELECT * FROM nation RIGHT (OUTER) JOIN region ON nation.name=region.name;
SELECT * FROM nation FULL (OUTER) JOIN region ON nation.name=region.name;

SELECT name, x, y FROM nation CROSS JOIN LATERAL (SELECT name || ' :-' AS x) CROSS JOIN LATERAL (SELECT x || ')' AS y);

SEMI JOIN、ANTI JOIN

当一张表在另一张表找到匹配的记录之后，半连接（semi-join）返回第一张表中的记录。与条件连接相反，即使在右节点中找到几条匹配的记录，左节点的表也只会返回一条记录。另外，右节点的表一条记录也不会返回。半连接通常使用IN或EXISTS作为连接条件。

而anti-join则与semi-join相反，即当在第二张表没有发现匹配记录时，才会返回第一张表里的记录；当使用not exists/not in的时候会用到。

其他支持的条件包括如下内容：

• where子句中的多个条件
• 别名关系
• 下标表达式
• 解引用表达式
• 强制转换表达式
• 特定函数调用

目前，只在如下情况下支持多个semi/anti join表达式：第一个表中的列在其直接后续的join表达式中被查询，且不与其他join表达式有关系。

示例如下：

CREATE SCHEMA testing ;

USE testing;

CREATE TABLE table1(id int, name varchar,rank int);

INSERT INTO table1 VALUES(10,'sachin',1),(45,'rohit',2),(46,'rohit',3),(18,'virat',4),(25,'dhawan',5);

CREATE TABLE table2(serial int,name varchar);

INSERT INTO table2 VALUES(1,'sachin'),(2,'sachin'),(3,'rohit'),(4,'virat');

CREATE TABLE table3(serial int, name varchar,country varchar);

INSERT INTO table3 VALUES(1,'sachin','india'),(20,'bhuvi','india'),(45,'boult','newzealand'),(3,'maxwell','australia'),(45,'rohit','india'),(4,'pant','india'),(10,'KL','india'),(445,'rohit','india');

CREATE TABLE table4(id int, name varchar,rank int);

INSERT INTO table4 VALUES(10,'sachin',1),(45,'rohit',2),(46,'rohit',3),(18,'virat',4),(25,'dhawan',5);

select * from table1 left semi join table2 on table1.name=table2.name where table1.name='rohit' and table2.serial=3;
 id | name  | rank 
----|-------|------
 45 | rohit |    2 
 46 | rohit |    3 
(2 rows)


select * from table1 left anti join table2 on table1.name=table2.name where table1.name='rohit' and table2.serial=3;
 id |  name  | rank 
----|--------|------
 10 | sachin |    1 
 18 | virat  |    4 
 25 | dhawan |    5 
(3 rows)

select * from table1 right semi join table2 on table1.name=table2.name where table1.name='rohit' and table2.serial=3;
 serial | name  
--------|-------
      3 | rohit 
(1 row)

select * from table1 right anti join table2 on table1.name=table2.name where table1.name='rohit' and table2.serial=3;
 serial |  name  
--------|--------
      1 | sachin 
      2 | sachin 
      4 | virat  
(3 rows)

SELECT * FROM table1 t1 LEFT SEMI JOIN table2 t2 on t1.name=t2.name left semi join table3 t3 on t1.name = t3.name left semi join table4 t4 on t1.name=t4.name;
id | name  | rank  
----|-------|------ 
 10 | sachin |   1  
 45 | rohit |    2
 46 | rohit |    3  
(3 rows) 

SELECT nation.name, region.name FROM nation CROSS JOIN region;
SELECT n.name, r.name FROM nation AS n CROSS JOIN region AS r;
SELECT n.name, r.name FROM nation n CROSS JOIN region r;