连接(Join)是将两个或多个表、视图或物化视图的结合在一起的查询。当查询的 FROM
子句中出现多个表时 OceanBase 数据库会进行连接查询,查询输出列可以从 FROM
子句任何表中选取。 如果多个表都有一个列名,那么您必须用表名限定查询过程中对这些列的所有引用。数据库中的连接类型一般包括 inner join
、outer join
、semi-join
和 anti-join
。其中 Semi-join
和 Anti-join
都是通过子查询改写得到,OceanBase 本身并没有表述 Anti-join
和 Semi-join
的语法。
连接条件
连接条件是将多表结合在一起的规则,存在于从句或 WHERE
子句中,用于比较来自不同的表两列,多数连接至少包含一个连接条件。连接条件可以分为等值连接(比如 t1.a = t2.b)和非等值连接(t1.a < t2.b),相比于非等值连接条件, 等值连接条件允许数据库使用高效的连接算法,比如 Hash Join
和 Merge-Sort join
。
为了执行连接,OceanBase 从不同表取行组合成对,使用连接条件进行匹配。 要执行两表以上的多表连接,OceanBase 首先根据比较它们的列的连接条件连接其中的两个表,然后根据包含连接表和新表列的连接条件将结果连接到另一个表。优化器根据连接条件、基表索引以及可用统计信息确定 OceanBase 连接顺序。
WHERE
子句除了连接条件还可能含有其他条件,这些仅引用一个表的条件可以进一步限制连接查询返回的行数。
等值连接(EQUI-JOINS)
等值连接(Equijoins)是连接条件包含等式运算符的连接。进行等值连接时将特定列满足等值条件的行进行组合输出。
自连接(SELF-JOIN)
自连接是表与其自身的连接。该表在 FROM
子句中出现两次,后跟表别名,这些别名限定联接条件中的列名。执行自连接时OceanBase数据库将组合并返回满足连接条件的行。
笛卡儿积(Cartesian Products)
如果连接查询中的两个表没有连接条件,OceanBase 数据库返回其笛卡尔乘积,使用第一个表的每一行与另一表每一个行进行组合输出。笛卡尔乘积总是生成许多行,很少有用。 例如,两个都有 100 行的表的笛卡尔积有 10,000 行。除非您特别需要笛卡尔乘积,否则始终包括一个连接条件。 如果查询连接了三个或多个表,并且没有为特定对指定连接条件,则优化器可以选择避免生成中间笛卡尔乘积的连接顺序。
内连接(INNER JOIN)
内连接(INNER JOIN)是数据库中最基本的连接操作。内连接基于连接条件将两张表(如 A 和 B)的列组合在一起,产生新的结果表。查询会将 A 表的每一行和 B 表的每一行进行比较,并找出满足连接条件的组合。当连接条件被满足,A 和 B 中匹配的行会按列组合(并排组合)成结果集中的一行。连接产生的结果集等于首先对两张表做笛卡尔积,将 A 中的每一行和 B 中的每一行组合,然后返回满足连接条件的记录。
外连接(OUTER JOIN)
外连接(OUTER JOIN) 返回满足连接条件的所有行,同时从一个表返回没有使用的行,在另一个表相应位置填充 NULL
。外连接可依据连接表保留左表、右表或全部表的行而进一步分为左连接、右连接和全连接。其中左连接(LEFT [OUTER] JOIN)中左表行未在右表匹配到行时,在右表自动填充 NULL
。右连接(RIGHT [OUTER] JOIN)中右表行未在左表匹配到行时,在左表自动填充 NULL
。全连接(FULL [OUTER] JOIN)左表或者右表未在其它表匹配到行时均会填充 NULL
。
SEMI 连接(SEMI-JOIN)
当 A 表和 B 表进行 LEFT
或 RIGHT ANTI-JOIN
的时候,它只返回 A 或 B 中所有能够在 B 或 A 中找到匹配的行。SEMI-JOIN
只能通过子查询展开得到。
ANTI 连接(ANTI-JOIN)
当 A 表和 B 表进行 LEFT
或 RIGHT ANTI-JOIN
的时候,它只返回 A 或 B 中所有不能在 B 或 A 中找到匹配的行。类似于 SEMI-JOIN
,ANTI-JOIN
也只能通过子查询展开得到。
示例
建立表 table_a 和表 table_b,并插入数据。执行以下语句:
CREATE TABLE table_a(PK INT, name VARCHAR(25));
INSERT INTO table_a VALUES(1,'福克斯');
INSERT INTO table_a VALUES(2,'警察');
INSERT INTO table_a VALUES(3,'的士');
INSERT INTO table_a VALUES(4,'林肯');
INSERT INTO table_a VALUES(5,'亚利桑那州');
INSERT INTO table_a VALUES(6,'华盛顿');
INSERT INTO table_a VALUES(7,'戴尔');
INSERT INTO table_a VALUES(10,'朗讯');
CREATE TABLE table_b(PK INT, name VARCHAR(25));
INSERT INTO table_b VALUES(1,'福克斯');
INSERT INTO table_b VALUES(2,'警察');
INSERT INTO table_b VALUES(3,'的士');
INSERT INTO table_b VALUES(6,'华盛顿');
INSERT INTO table_b VALUES(7,'戴尔');
INSERT INTO table_b VALUES(8,'微软');
INSERT INTO table_b VALUES(9,'苹果');
INSERT INTO table_b VALUES(11,'苏格兰威士忌');
自连接查询(SELF-JOIN):
SELECT * FROM table_a ta, table_a tb WHERE ta.NAME = tb.NAME;
查询结果如下:
+------+-----------------+------+-----------------+
| PK | NAME | PK | NAME |
+------+-----------------+------+-----------------+
| 1 | 福克斯 | 1 | 福克斯 |
| 2 | 警察 | 2 | 警察 |
| 3 | 的士 | 3 | 的士 |
| 4 | 林肯 | 4 | 林肯 |
| 5 | 亚利桑那州 | 5 | 亚利桑那州 |
| 6 | 华盛顿 | 6 | 华盛顿 |
| 7 | 戴尔 | 7 | 戴尔 |
| 10 | 朗讯 | 10 | 朗讯 |
+------+-----------------+------+-----------------+
内连接(INNER JOIN)查询:
SELECT A.PK AS A_PK, A.name AS A_Value, B.PK AS B_PK, B.name AS B_Value
FROM table_a A INNER JOIN table_b B ON A.PK = B.PK;
查询结果如下:
+------+-----------+------+-----------+
| A_PK | A_VALUE | B_PK | B_VALUE |
+------+-----------+------+-----------+
| 1 | 福克斯 | 1 | 福克斯 |
| 2 | 警察 | 2 | 警察 |
| 3 | 的士 | 3 | 的士 |
| 6 | 华盛顿 | 6 | 华盛顿 |
| 7 | 戴尔 | 7 | 戴尔 |
+------+-----------+------+-----------+
左连接查询:
SELECT A.PK AS A_PK, A.name AS A_Value, B.PK AS B_PK, B.name AS B_Value
FROM table_a A LEFT JOIN table_b B ON A.PK = B.PK;
查询结果如下:
+------+-----------------+------+-----------+
| A_PK | A_VALUE | B_PK | B_VALUE |
+------+-----------------+------+-----------+
| 1 | 福克斯 | 1 | 福克斯 |
| 2 | 警察 | 2 | 警察 |
| 3 | 的士 | 3 | 的士 |
| 6 | 华盛顿 | 6 | 华盛顿 |
| 7 | 戴尔 | 7 | 戴尔 |
| 4 | 林肯 | NULL | NULL |
| 5 | 亚利桑那州 | NULL | NULL |
| 10 | 朗讯 | NULL | NULL |
+------+-----------------+------+-----------+
右连接查询:
obclient> SELECT A.PK AS A_PK, A.name AS A_Value, B.PK AS B_PK, B.name AS B_Value FROM table_a A RIGHT JOIN table_b B ON A.PK = B.PK;
查询结果如下:
+------+-----------+------+--------------------+
| A_PK | A_VALUE | B_PK | B_VALUE |
+------+-----------+------+--------------------+
| 1 | 福克斯 | 1 | 福克斯 |
| 2 | 警察 | 2 | 警察 |
| 3 | 的士 | 3 | 的士 |
| 6 | 华盛顿 | 6 | 华盛顿 |
| 7 | 戴尔 | 7 | 戴尔 |
| NULL | NULL | 8 | 微软 |
| NULL | NULL | 9 | 苹果 |
| NULL | NULL | 11 | 苏格兰威士忌 |
+------+-----------+------+--------------------+
全连接查询:
obclient> SELECT A.PK AS A_PK,A.name AS A_Value,B.PK AS B_PK,B.name AS B_Value FROM table_a A FULL JOIN table_b B ON A.PK = B.PK;
查询结果如下:
+------+-----------------+------+--------------------+
| A_PK | A_VALUE | B_PK | B_VALUE |
+------+-----------------+------+--------------------+
| 1 | 福克斯 | 1 | 福克斯 |
| 2 | 警察 | 2 | 警察 |
| 3 | 的士 | 3 | 的士 |
| 6 | 华盛顿 | 6 | 华盛顿 |
| 7 | 戴尔 | 7 | 戴尔 |
| NULL | NULL | 8 | 微软 |
| NULL | NULL | 9 | 苹果 |
| NULL | NULL | 11 | 苏格兰威士忌 |
| 4 | 林肯 | NULL | NULL |
| 5 | 亚利桑那州 | NULL | NULL |
| 10 | 朗讯 | NULL | NULL |
+------+-----------------+------+--------------------+
Semi 连接(semi-join):有依赖关系的子查询被展开改写成 SEMI-JOIN。
explain SELECT * FROM table_a t1 WHERE t1.PK IN (SELECT t2.PK FROM table_b t2 WHERE t2.NAME = t1.NAME);
查询结果如下:
+------------------------------------------------+
| Query Plan |
+------------------------------------------------+
=======================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
---------------------------------------
|0 |HASH SEMI JOIN| |8 |114 |
|1 | TABLE SCAN |T1 |8 |38 |
|2 | TABLE SCAN |T2 |8 |38 |
=======================================
Outputs & filters:
-------------------------------------
0 - output([T1.PK], [T1.NAME]), filter(nil),
equal_conds([T1.PK = T2.PK], [T2.NAME = T1.NAME]), other_conds(nil)
1 - output([T1.NAME], [T1.PK]), filter(nil),
access([T1.NAME], [T1.PK]), partitions(p0)
2 - output([T2.NAME], [T2.PK]), filter(nil),
access([T2.NAME], [T2.PK]), partitions(p0)
+------------------------------------------------+
Anti 连接(anti-join):有依赖关系的子查询被改写成 Anti-join
。
EXPLAIN SELECT * FROM table_a t1 WHERE t1.PK NOT IN (SELECT t2.PK
FROM table_b t2 WHERE t2.name = t1.name);
查询结果如下:
+------------------------------------------------+
| Query Plan |
+------------------------------------------------+
=======================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
---------------------------------------
|0 |HASH ANTI JOIN| |0 |112 |
|1 | TABLE SCAN |T1 |8 |38 |
|2 | TABLE SCAN |T2 |8 |38 |
=======================================
Outputs & filters:
-------------------------------------
0 - output([T1.PK], [T1.NAME]), filter(nil),
equal_conds([T2.NAME = T1.NAME]), other_conds([(T_OP_OR, T1.PK = T2.PK,
(T_OP_IS, T1.PK, NULL, 0), (T_OP_IS, T2.PK, NULL, 0))])
1 - output([T1.NAME], [T1.PK]), filter(nil),
access([T1.NAME], [T1.PK]), partitions(p0)
2 - output([T2.NAME], [T2.PK]), filter(nil),
access([T2.NAME], [T2.PK]), partitions(p0)
+---------------------------------------------------------+
作者:terry,如若转载,请注明出处:https://www.web176.com/oceanbase/26222.html