跳到主要内容

联接

数据库中的联接语句用于将数据库中的两个或多个表根据联接条件组合起来。由"联接"生成的集合, 可以被保存为表，或者当成表来使用。

联接语句的含义是把两张表的属性通过它们的值组合在一起。数据库中的联接类型一般包括内联接（Inner-Join），外联接（Outer-Join），Semi 联接（Semi-Join）和 Anti 联接（Anti-Join）。其中 Semi-Join 和 Anti-Join 都是通过子查询改写得到，SQL 本身并没有表述 Anti-Join 和 Semi-Join 的语法。

联接条件​

联接条件可以分为等值联接（例如 t1.a = t2.b）和非等值联接（例如 t1.a < t2.b）。相比于非等值联接条件，等值联接条件的一个好处是允许数据库中使用高效的联接算法，例如 Hash Join 和 Merge-Sort Join。

Self-Join​

Self-Join 是指表与自身进行联接的 Join 操作。下面展示了一个 Self-Join 的例子。

CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT);
Query OK, 0 rows affected

SELECT * FROM t1 AS ta, t1 AS tb WHERE ta.b = tb.b;

内联接（Inner-Join）​

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

CREATE TABLE t1(c1 INT,c2 INT);
Query OK, 0 rows affected
CREATE TABLE t2(c1 INT,c2 INT);
Query OK, 0 rows affected

SELECT * FROM t1 JOIN t2 USING(c1);

外联接（Outer-Join）​

Outer-Join 并不要求联接的两表的每一条记录在对方表中都一条匹配的记录。要保留所有记录（甚至这条记录没有匹配的记录也要保留）的表称为保留表。

外联接可依据联接表保留左表，右表或全部表的行而进一步分为左外联接，右外联接和全联接。

  • 左外联接中左表的一行未在右表中找到的时候，就在右表自动填充 NULL。

  • 右外联接中右表的一行未在左表中找到的时候，就在左表自动填充 NULL。

  • 全联接就是左表或者右表找不匹配行的时候都会自动填充 NULL。

CREATE TABLE t1(c1 INT,c2 INT);
Query OK, 0 rows affected
CREATE TABLE t2(c1 INT,c2 INT);
Query OK, 0 rows affected

SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1;
SELECT * FROM t1 FULL JOIN t2 ON t1.c1 = t2.c1;

Semi 联接 (Semi-Join)​

当 A 表和 B 表进行 left/right Semi-Join 的时候，它只返回 A 表中所有能够在 B 表中找到匹配的行或者 B 表中所有能够在 A 表中找到匹配的行。

Semi-Join 只能通过子查询展开得到，如下所示。

CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT);
Query OK, 0 rows affected

CREATE TABLE t2(a INT PRIMARY KEY, b INT, c INT);
Query OK, 0 rows affected

INSERT INTO t1 VALUES (1, 1, 1),(2, 2, 2);
INSERT INTO t2 VALUES (1, 1, 1),(2, 2, 2);

SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE t2.c = t1.c);

通过 EXPLAIN 查看查询计划时，可以看到有依赖关系的子查询被展开改写成 Semi-Join。

EXPLAIN SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE t2.c = t1.c);
| ========================================
|ID|OPERATOR       |NAME|EST. ROWS|COST|
----------------------------------------
|0 |MERGE SEMI JOIN|    |2        |76  |
|1 | TABLE SCAN    |t1  |2        |37  |
|2 | SORT          |    |2        |38  |
|3 |  TABLE SCAN   |t2  |2        |37  |
========================================
...

Anti 联接（Anti-Join）​

当 A 表和 B 表进行 left/right Anti-Join 的时候，当 A 表和 B 表进行 left/right Anti-Join 的时候，它只返回 A 表中所有不能在 B 表中找到匹配的行或者 B 表中所有不能在 A 表中找到匹配的行。

类似于 Semi-Join，Anti-Join 也只能通过子查询展开得到，如下所示。

CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT);
Query OK, 0 rows affected

CREATE TABLE t2(a INT PRIMARY KEY, b INT, c INT);
Query OK, 0 rows affected

INSERT INTO t1 VALUES (1, 1, 1),(2, 2, 2);
INSERT INTO t2 VALUES (1, 1, 1),(2, 2, 2);

SELECT * FROM t1 WHERE t1.a NOT IN (SELECT t2.b FROM t2 WHERE t2.c = t1.c);

通过 EXPLAIN 查看查询计划时，可以看到有依赖关系的子查询被改写成 Anti-Join：

EXPLAIN SELECT * FROM t1 WHERE t1.a NOT IN (SELECT t2.b FROM t2 WHERE t2.c = t1.c);
| =============================================
|ID|OPERATOR            |NAME|EST. ROWS|COST|
---------------------------------------------
|0 |HASH RIGHT ANTI JOIN|    |0        |77  |
|1 | TABLE SCAN         |t2  |2        |37  |
|2 | TABLE SCAN         |t1  |2        |37  |
=============================================
...

Contents

编辑此页