全部产品

连接

更新时间:2020-03-20 17:12:55

连接(Join)是将来自两个或多个表、视图或实例视图的行组合在一起的查询。 每当查询的 FROM 子句中出现多个表时,OceanBase 数据库执行连接。 查询的选择列表可以从其中任何表中选择任何列。 如果这两个表都有一个列名,那么您必须用表名限定查询过程中对这些列的所有引用。数据库中的连接类型一般包括 inner joinouter joinsemi-joinanti-join。其中 Semi-joinAnti-join 都是通过子查询改写得到,OceanBase 本身并没有表述 Anti-joinSemi-join 的语法。

连接条件

连接条件可以分为等值连接(比如 t1.a = t2.b)和非等值连接(t1.a < t2.b)。相比于非等值连接条件, 等值连接条件的一个好处是允许数据库中使用高效的连接算法,比如 Hash JoinMerge-Sort join

大多数连接查询在在从句或 WHERE 子句中至少包含一个连接条件。 连接条件比较两列,每列来自不同的表。 为了执行连接,OceanBase 将每行组合成对,每个行包含每个表中的一行。 连接条件中的列也不需要出现在选择列表中。 要执行三个或多个表的连接,OceanBase 首先根据比较它们的列的连接条件连接其中的两个表,然后根据包含连接表和新表的列的连接条件将结果连接到另一个表。优化器根据连接条件、表上的索引以及表的任何可用统计数据确定 OceanBase 连接表的顺序。 包含连接条件的 WHERE 子句也可以包含其他条件,这些条件仅引用一个表的列。 这些条件可以进一步限制连接查询返回的行。 如果 WHERE 子句包含连接条件,则不能在 WHERE 子句中指定 LOB 列。

等值连接(EQUI-JOIN)

等值连接(Equijoins)是包含等式运算符连接条件的连接。等值连接组合了具有指定列的等效值的行。根据优化器选择执行连接的内部算法,单个表中 Equijoins 连接条件下的列的总大小可能仅限于数据块的大小减去一些开销。数据块的大小由初始化参数 DB_BLOCK_SIZE 指定。

自连接(SELF-JOIN)

自连接是表与其自身的连接。该表在 FROM 子句中出现两次,后跟表别名,这些别名限定连接条件中的列名。为了执行自连接,OceanBase 数据库将合并并返回满足连接条件的表行。

笛卡儿积(Cartesian Products)

如果连接查询中的两个表没有连接条件,则 OceanBase 数据库返回其笛卡尔乘积。 OceanBase 将一个表的每一行与另一行合并。笛卡尔乘积总是生成许多行,并且很少有用。 例如,两个都有 100 行的表的笛卡尔积有 10,000 行。除非您特别需要笛卡尔乘积,否则始终包括一个连接条件。 如果查询连接了三个或多个表,并且没有为特定对指定连接条件,则优化器可以选择避免生成中间笛卡尔乘积的连接顺序。

内连接(INNER JOIN)

内连接(INNER JOIN)是数据库中最基本的连接操作。内连接基于连接条件将两张表(如 A 和 B)的列组合在一起,产生新的结果表。查询会将 A 表的每一行和 B 表的每一行进行比较,并找出满足连接条件的组合。当连接条件被满足,A 和 B 中匹配的行会按列组合(并排组合)成结果集中的一行。连接产生的结果集,可以定义为首先对两张表做笛卡尔积(交叉连接) — 将 A 中的每一行和 B 中的每一行组合,然后返回满足连接条件的记录。

外连接(OUTER JOIN)

外连接(OUTER JOIN)扩展了简单连接的结果。 外部连接返回满足连接条件的所有行,并从一个表中返回部分或所有这些行,而另一个表中没有行满足连接条件。外连接可依据连接表保留左表、右表或全部表的行而进一步分为左连接、右连接和全连接。其中左连接(LEFT [OUTER] JOIN)中左表的一行未在右表中找到,就会在右表自动填充 NULL。右连接(RIGHT [OUTER] JOIN)中右表的一行未在左表中找到的时候,就在左表自动填充 NULL。全连接(FULL [OUTER] JOIN)就是左表或者右表找不匹配行的时候都会自动填充。

SEMI 连接(SEMI-JOIN)

当 A 表和 B 表进行 LEFTRIGHT ANTI-JOIN 的时候,它只返回 A 或 B 中所有能够在 B 或 A 中找到匹配的行。SEMI-JOIN 只能通过子查询展开得到。

ANTI 连接(ANTI-JOIN)

当 A 表和 B 表进行 LEFTRIGHT ANTI-JOIN 的时候,它只返回 A 或 B 中所有不能在 B 或 A 中找到匹配的行。类似于 SEMI-JOINANTI-JOIN 也只能通过子查询展开得到。

示例

建立表 table_a 和表 table_b,并插入数据。执行以下语句:

  1. CREATE TABLE table_a(PK INT, name VARCHAR(25));
  2. INSERT INTO table_a VALUES(1,'福克斯');
  3. INSERT INTO table_a VALUES(2,'警察');
  4. INSERT INTO table_a VALUES(3,'的士');
  5. INSERT INTO table_a VALUES(4,'林肯');
  6. INSERT INTO table_a VALUES(5,'亚利桑那州');
  7. INSERT INTO table_a VALUES(6,'华盛顿');
  8. INSERT INTO table_a VALUES(7,'戴尔');
  9. INSERT INTO table_a VALUES(10,'朗讯');
  10. CREATE TABLE table_b(PK INT, name VARCHAR(25));
  11. INSERT INTO table_b VALUES(1,'福克斯');
  12. INSERT INTO table_b VALUES(2,'警察');
  13. INSERT INTO table_b VALUES(3,'的士');
  14. INSERT INTO table_b VALUES(6,'华盛顿');
  15. INSERT INTO table_b VALUES(7,'戴尔');
  16. INSERT INTO table_b VALUES(8,'微软');
  17. INSERT INTO table_b VALUES(9,'苹果');
  18. INSERT INTO table_b VALUES(11,'苏格兰威士忌');

自连接查询(SELF-JOIN)

  1. SELECT * FROM table_a ta, table_a tb WHERE ta.NAME = tb.NAME;

查询结果如下:

  1. +------+-----------------+------+-----------------+
  2. | PK | NAME | PK | NAME |
  3. +------+-----------------+------+-----------------+
  4. | 1 | 福克斯 | 1 | 福克斯 |
  5. | 2 | 警察 | 2 | 警察 |
  6. | 3 | 的士 | 3 | 的士 |
  7. | 4 | 林肯 | 4 | 林肯 |
  8. | 5 | 亚利桑那州 | 5 | 亚利桑那州 |
  9. | 6 | 华盛顿 | 6 | 华盛顿 |
  10. | 7 | 戴尔 | 7 | 戴尔 |
  11. | 10 | 朗讯 | 10 | 朗讯 |
  12. +------+-----------------+------+-----------------+

内连接查询(INNER JOIN)

  1. SELECT A.PK AS A_PK, A.name AS A_Value, B.PK AS B_PK, B.name AS B_Value
  2. FROM table_a A INNER JOIN table_b B ON A.PK = B.PK;

查询结果如下:

  1. +------+-----------+------+-----------+
  2. | A_PK | A_VALUE | B_PK | B_VALUE |
  3. +------+-----------+------+-----------+
  4. | 1 | 福克斯 | 1 | 福克斯 |
  5. | 2 | 警察 | 2 | 警察 |
  6. | 3 | 的士 | 3 | 的士 |
  7. | 6 | 华盛顿 | 6 | 华盛顿 |
  8. | 7 | 戴尔 | 7 | 戴尔 |
  9. +------+-----------+------+-----------+

左连接查询

  1. SELECT A.PK AS A_PK, A.name AS A_Value, B.PK AS B_PK, B.name AS B_Value
  2. FROM table_a A LEFT JOIN table_b B ON A.PK = B.PK;

查询结果如下:

  1. +------+-----------------+------+-----------+
  2. | A_PK | A_VALUE | B_PK | B_VALUE |
  3. +------+-----------------+------+-----------+
  4. | 1 | 福克斯 | 1 | 福克斯 |
  5. | 2 | 警察 | 2 | 警察 |
  6. | 3 | 的士 | 3 | 的士 |
  7. | 6 | 华盛顿 | 6 | 华盛顿 |
  8. | 7 | 戴尔 | 7 | 戴尔 |
  9. | 4 | 林肯 | NULL | NULL |
  10. | 5 | 亚利桑那州 | NULL | NULL |
  11. | 10 | 朗讯 | NULL | NULL |
  12. +------+-----------------+------+-----------+

右连接查询

  1. 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;

查询结果如下:

  1. +------+-----------+------+--------------------+
  2. | A_PK | A_VALUE | B_PK | B_VALUE |
  3. +------+-----------+------+--------------------+
  4. | 1 | 福克斯 | 1 | 福克斯 |
  5. | 2 | 警察 | 2 | 警察 |
  6. | 3 | 的士 | 3 | 的士 |
  7. | 6 | 华盛顿 | 6 | 华盛顿 |
  8. | 7 | 戴尔 | 7 | 戴尔 |
  9. | NULL | NULL | 8 | 微软 |
  10. | NULL | NULL | 11 | 苏格兰威士忌 |
  11. | NULL | NULL | 9 | 苹果 |
  12. +------+-----------+------+--------------------+

全连接查询

  1. 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;

查询结果如下:

  1. +------+-----------------+------+--------------------+
  2. | A_PK | A_VALUE | B_PK | B_VALUE |
  3. +------+-----------------+------+--------------------+
  4. | 1 | 福克斯 | 1 | 福克斯 |
  5. | 2 | 警察 | 2 | 警察 |
  6. | 3 | 的士 | 3 | 的士 |
  7. | 6 | 华盛顿 | 6 | 华盛顿 |
  8. | 7 | 戴尔 | 7 | 戴尔 |
  9. | NULL | NULL | 8 | 微软 |
  10. | NULL | NULL | 9 | 苹果 |
  11. | NULL | NULL | 11 | 苏格兰威士忌 |
  12. | 4 | 林肯 | NULL | NULL |
  13. | 5 | 亚利桑那州 | NULL | NULL |
  14. | 10 | 朗讯 | NULL | NULL |
  15. +------+-----------------+------+--------------------+

Semi 连接查询(semi-join):有依赖关系的子查询被展开改写成 SEMI-JOIN

  1. explain SELECT * FROM table_a t1 WHERE t1.PK IN (SELECT t2.PK FROM table_b t2 WHERE t2.NAME = t1.NAME);

查询结果如下:

  1. +------------------------------------------------+
  2. | Query Plan |
  3. +------------------------------------------------+
  4. =======================================
  5. |ID|OPERATOR |NAME|EST. ROWS|COST|
  6. ---------------------------------------
  7. |0 |HASH SEMI JOIN| |8 |114 |
  8. |1 | TABLE SCAN |T1 |8 |38 |
  9. |2 | TABLE SCAN |T2 |8 |38 |
  10. =======================================
  11. Outputs & filters:
  12. -------------------------------------
  13. 0 - output([T1.PK], [T1.NAME]), filter(nil),
  14. equal_conds([T1.PK = T2.PK], [T2.NAME = T1.NAME]), other_conds(nil)
  15. 1 - output([T1.NAME], [T1.PK]), filter(nil),
  16. access([T1.NAME], [T1.PK]), partitions(p0)
  17. 2 - output([T2.NAME], [T2.PK]), filter(nil),
  18. access([T2.NAME], [T2.PK]), partitions(p0)
  19. +------------------------------------------------+

Anti 连接查询(anti-join):有依赖关系的子查询被改写成 Anti-join

  1. EXPLAIN SELECT * FROM table_a t1 WHERE t1.PK NOT IN (SELECT t2.PK
  2. FROM table_b t2 WHERE t2.name = t1.name);

查询结果如下:

  1. +------------------------------------------------+
  2. | Query Plan |
  3. +------------------------------------------------+
  4. =======================================
  5. |ID|OPERATOR |NAME|EST. ROWS|COST|
  6. ---------------------------------------
  7. |0 |HASH ANTI JOIN| |0 |112 |
  8. |1 | TABLE SCAN |T1 |8 |38 |
  9. |2 | TABLE SCAN |T2 |8 |38 |
  10. =======================================
  11. Outputs & filters:
  12. -------------------------------------
  13. 0 - output([T1.PK], [T1.NAME]), filter(nil),
  14. equal_conds([T2.NAME = T1.NAME]), other_conds([(T_OP_OR, T1.PK = T2.PK,
  15. (T_OP_IS, T1.PK, NULL, 0), (T_OP_IS, T2.PK, NULL, 0))])
  16. 1 - output([T1.NAME], [T1.PK]), filter(nil),
  17. access([T1.NAME], [T1.PK]), partitions(p0)
  18. 2 - output([T2.NAME], [T2.PK]), filter(nil),
  19. access([T2.NAME], [T2.PK]), partitions(p0)
  20. +---------------------------------------------------------+