Incepter 中的 join 语句测试

step1 建测试表

CREATE TABLE t1
(id INT
  , na STRING)
CLUSTERED BY(id)
INTO 1 BUCKETS
STORED AS ORC
TBLPROPERTIES("transactional" = "true");

INSERT INTO t1 VALUES(1,'a');
INSERT INTO t1 VALUES(2,'b');
INSERT INTO t1 VALUES(3,'i');


CREATE TABLE t2
(id INT
  , na STRING)
CLUSTERED BY(id)
INTO 1 BUCKETS
STORED AS ORC
TBLPROPERTIES("transactional" = "true");

INSERT INTO t2 VALUES(1,'c');
INSERT INTO t2 VALUES(1,'c');
INSERT INTO t2 VALUES(2,'d');
INSERT INTO t2 VALUES(2,'d');
INSERT INTO t2 VALUES(4,'j');

                                  

 

step 2 测试 JOIN  ， LEFT OUTER  JOIN

SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id;

结果：

 

两表交换位置

SELECT * FROM t2 LEFT JOIN t1 ON t1.id=t2.id;

测试 LEFT OUTER  JOIN 和 JOIN 的返回结果没有区别。

step2 测试  INNER JOIN

SELECT * FROM t1 INNER JOIN t2 ON t1.id=t2.id; 

SELECT * FROM t2 INNER JOIN t1 ON t1.id=t2.id; 

两条语句返回的结果一致：

 step3 测试 LEFT SEMI JOIN

SELECT * FROM t1 LEFT SEMI JOIN t2 ON t1.id=t2.id; 

SELECT * FROM t2 LEFT SEMI JOIN t1 ON t1.id=t2.id; 

结果分别如下，可以显示左边的表在右边表里面有等值的行，这里只针对左边的表进行输出。

incepter 中如下的not in 语句是被支持的

select t1.id from t1 where id not in(select id from t2);