GBase 8a MPP Cluster 产品手册 5 数据库管理指南 文档版本953(2022-04-10) 南大通用数据技术股份有限公司 1077 参数名称 说 明 2)a LEFT JOIN b ON a.c1 = b.c1 AND a.c2 = b.c2 AND a.c3 = b.c3 Vc_name vc 命,可选项。 database_name 数据库名,可选项。 table_name 表名 示例 示例1:join 语法示例。 示例中所用的表及数据: CREATE TABLE t1(id INT,name VARCHAR(30)); CREATE TABLE t2(id INT,title VARCHAR(20),name VARCHAR(30)); INSERT INTO t1 VALUES(1,'name1'),(2,'name2'),(3,'name3'); INSERT INTO t2 VALUES(1,'t1','name1'),(3,'t3','name3'),(4,'t4','name4'); gbase> SELECT * FROM t1; +------+-------+ | id | name | +------+-------+ | 1 | name1 | | 2 | name2 | | 3 | name3 | +------+-------+ 3 rows in set (Elapsed: 00:00:00.07) gbase> SELECT * FROM t2; +------+-------+-------+ | id | title | name | +------+-------+-------+ | 1 | t1 | name1 | | 3 | t3 | name3 | | 4 | t4 | name4 | +------+-------+-------+ 3 rows in set (Elapsed: 00:00:00.03) INNER JOIN: gbase> SELECT a.id,a.name,b.name,b.title FROM t1 a INNER JOIN t2 b ON
GBase 8a MPP Cluster 产品手册 5 数据库管理指南 文档版本953(2022-04-10) 南大通用数据技术股份有限公司 1078 a.id=b.id; +------+-------+-------+-------+ | id | name | name | title | +------+-------+-------+-------+ | 1 | name1 | name1 | t1 | | 3 | name3 | name3 | t3 | +------+-------+-------+-------+ 2 rows in set (Elapsed: 00:00:00.15) WHERE 子句方式,等价于上面的INNER JON: gbase> SELECT a.id,a.name,b.name,b.title FROM t1 a,t2 b WHERE a.id=b.id; +------+-------+-------+-------+ | id | name | name | title | +------+-------+-------+-------+ | 1 | name1 | name1 | t1 | | 3 | name3 | name3 | t3 | +------+-------+-------+-------+ 2 rows in set (Elapsed: 00:00:00.12) LEFT JOIN: gbase> SELECT a.id,a.name,b.name,b.title FROM t1 a LEFT JOIN t2 b ON a.id=b.id; +------+-------+-------+-------+ | id | name | name | title | +------+-------+-------+-------+ | 2 | name2 | NULL | NULL | | 1 | name1 | name1 | t1 | | 3 | name3 | name3 | t3 | +------+-------+-------+-------+ 3 rows in set (Elapsed: 00:00:00.13) RHGHT JOIN: gbase> SELECT a.id,a.name,b.name,b.title FROM t1 a RIGHT JOIN t2 b ON a.id=b.id; +------+-------+-------+-------+ | id | name | name | title | +------+-------+-------+-------+ | NULL | NULL | name4 | t4 | | 1 | name1 | name1 | t1 | | 3 | name3 | name3 | t3 | +------+-------+-------+-------+ 3 rows in set (Elapsed: 00:00:00.16)
GBase 8a MPP Cluster 产品手册 5 数据库管理指南 文档版本953(2022-04-10) 南大通用数据技术股份有限公司 1079 FULL JOIN: gbase> SELECT a.id,a.name,b.name,b.title FROM t1 a FULL JOIN t2 b ON a.id=b.id; +------+-------+-------+-------+ | id | name | name | title | +------+-------+-------+-------+ | 2 | name2 | NULL | NULL | | NULL | NULL | name4 | t4 | | 1 | name1 | name1 | t1 | | 3 | name3 | name3 | t3 | +------+-------+-------+-------+ 4 rows in set (Elapsed: 00:00:00.17) 示例2:char JOIN char(相同精度)示例。 示例中所用的表及数据: CREATE TABLE t3(a CHAR(5), b CHAR(10)); CREATE TABLE t4(a CHAR(10)); INSERT INTO t3 VALUES('abcde', 'abcde'); INSERT INTO t4 VALUES('abcde'); gbase> SELECT * FROM t3; +-------+------------+ | a | b | +-------+------------+ | abcde | abcde | +-------+------------+ 1 row in set (Elapsed: 00:00:00.03) gbase> SELECT * FROM t4; +------------+ | a | +------------+ | abcde | +------------+ 1 row in set (Elapsed: 00:00:00.02) t3.b 与t4.a 类型均为char(10),因此可以建立等值JOIN 关系: gbase> SELECT * FROM t3 JOIN t4 ON t3.b = t4.a; +-------+------------+------------+ | a | b | a | +-------+------------+------------+
GBase 8a MPP Cluster 产品手册 5 数据库管理指南 文档版本953(2022-04-10) 南大通用数据技术股份有限公司 1080 | abcde | abcde | abcde | +-------+------------+------------+ 1 row in set (Elapsed: 00:00:00.06) t3.a 类型为char(5),t4.a 类型为char(10),不同精度的char 类型间 禁止建立等值JOIN 关系: gbase> SELECT * FROM t3 JOIN t4 ON t3.a = t4.a; ERROR 1149 (42000): (GBA-02SC-1001) Data types of equivalence join relation ((`vc1.demo.t3`.`a` = `vc1.demo.t4`.`a`)) are not supported , data types: left is CHAR(5), right is CHAR(10) 示例3:char JOIN varchar 示例。 示例中所用的表及数据: CREATE TABLE t1(a CHAR(5), b CHAR(10)); CREATE TABLE t2(a varchar(10)); INSERT INTO t1 VALUES('abcde', 'abcde'); INSERT INTO t2 VALUES('abcde'); T1.a 类型时char(5), ‘abcde’ 不需要补充空格, 与varchar 的'abcde' 相等: gbase> SELECT * FROM t1 JOIN t2 ON t1.a = t2.a; +-------+------------+-------+ | a | b | a | +-------+------------+-------+ | abcde | abcde | abcde | +-------+------------+-------+ 1 row in set t1.b 类型是char(10),补齐空格后为'abcde ',与varchar 的'abcde' 不等,以下面结果集为空: gbase> SELECT * FROM t1 JOIN t2 ON t1.b = t2.a; Empty set 示例4:int JOIN varchar 示例。 示例中所用的表及数据: CREATE TABLE t5(a INT); CREATE TABLE t6(a VARCHAR(10)); INSERT INTO t5 VALUES(179); INSERT INTO t6 VALUES('179');
GBase 8a MPP Cluster 产品手册 5 数据库管理指南 文档版本953(2022-04-10) 南大通用数据技术股份有限公司 1081 int 和varchar 间可以建立等值JOIN 关系: gbase> SELECT * FROM t5 JOIN t6 ON t5.a = t6.a; +------+------+ | a | a | +------+------+ | 179 | 179 | +------+------+ 1 row in set