mysql-joins具体用法说明

吾爱主题阅读：148 2024-04-02 08:00:21 评论：0

Join 语法：
1、Inner JOIN：（内连接）
2、Left JOIN：（左连接）
3、Left Excluding JOIN：（左连接排除内连接结果）
4、Right JOIN：（右连接）
5、Right Excluding JOIN：（右连接排除内连接结果）
6、Outer JOIN：（外连接）
7、Outer Excluding JOIN：（外连接排除内连接结果）

join对于接触过数据库的人，这个词都不陌生，而且很多人很清楚各种join，还有很多人对这个理解也不是很透彻。
假设我们有两个表，table_a和table_b。这两个表中的数据如下所示：

1 2 3 4 5 6 7 8 9 10 11 table_a | table_b pk value | pk value ---- ---------- | ---- ---------- 1 fox | 1 trot 2 cop | 2 car 3 taxi | 3 cab 6 washington | 6 monument 7 dell | 7 pc 5 arizona | 8 microsoft 4 lincoln | 9 apple 10 lucent | 11 scotch

join 语法：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 join_table: table_reference join table_factor [join_condition] //内连接 | table_reference { left | right | full } [ outer ] join table_reference join_condition //外连接 | table_reference left semi join table_reference join_condition //左半连接 | table_reference cross join table_reference [join_condition] ( as of hive 0.10) table_reference: table_factor //表 | join_table // join 语句 table_factor: tbl_name [alias] //表名[别名] | table_subquery alias //子查寻[别名] | ( table_references ) //带空号的table_reference join_condition: on expression // on 开头的条件语句

1、inner join：（内连接）

这是最简单、最容易理解的连接，也是最常见的连接。此查询将返回左表(表a)中具有右表(表b)中匹配记录的所有记录。此连接写成如下：

1 2 3 4 select <select_list> from table_a a inner join table_b b on a. key = b. key

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 -- inner join select a.pk as a_pk, a.value as a_value, b.value as b_value, b.pk as b_pk from table_a a inner join table_b b on a.pk = b.pk a_pk a_value b_value b_pk ---- ---------- ---------- ---- 1 fox trot 1 2 cop car 2 3 taxi cab 3 6 washington monument 6 7 dell pc 7 (5 row(s) affected)

2、left join：（左连接）

此查询将返回左表(表a)中的所有记录，而不管这些记录是否与右表(表b)中的任何记录匹配。它还将从正确的表中返回任何匹配的记录。此连接写成如下：

1 2 3 4 select <select_list> from table_a a left join table_b b on a. key = b. key

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 -- left join select a.pk as a_pk, a.value as a_value, b.value as b_value, b.pk as b_pk from table_a a left join table_b b on a.pk = b.pk a_pk a_value b_value b_pk ---- ---------- ---------- ---- 1 fox trot 1 2 cop car 2 3 taxi cab 3 4 lincoln null null 5 arizona null null 6 washington monument 6 7 dell pc 7 10 lucent null null (8 row(s) affected)

3、left excluding join：（左连接排除内连接结果）

此查询将返回左表(表a)中与右表(表b)中的任何记录都不匹配的所有记录。此连接写成如下：

1 2 3 4 5 select <select_list> from table_a a left join table_b b on a. key = b. key where b. key is null

1 2 3 4 5 6 7 8 9 10 11 12 13 14 -- left excluding join select a.pk as a_pk, a.value as a_value, b.value as b_value, b.pk as b_pk from table_a a left join table_b b on a.pk = b.pk where b.pk is null a_pk a_value b_value b_pk ---- ---------- ---------- ---- 4 lincoln null null 5 arizona null null 10 lucent null null (3 row(s) affected)

4、right join：（右连接）

此查询将返回右表(表b)中的所有记录，而不管这些记录中是否有任何记录与左表(表a)中的记录相匹配。它还将返回左表中的任何匹配记录。此连接写成如下：

1 2 3 4 select <select_list> from table_a a right join table_b b on a. key = b. key

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 -- right join select a.pk as a_pk, a.value as a_value, b.value as b_value, b.pk as b_pk from table_a a right join table_b b on a.pk = b.pk a_pk a_value b_value b_pk ---- ---------- ---------- ---- 1 fox trot 1 2 cop car 2 3 taxi cab 3 6 washington monument 6 7 dell pc 7 null null microsoft 8 null null apple 9 null null scotch 11 (8 row(s) affected)

5、right excluding join：（右连接排除内连接结果）

此查询将返回右表(表b)中与左表(表a)中的任何记录都不匹配的所有记录。此连接写成如下：

1 2 3 4 5 select <select_list> from table_a a right join table_b b on a. key = b. key where a. key is null

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 -- right excluding join select a.pk as a_pk, a.value as a_value, b.value as b_value, b.pk as b_pk from table_a a right join table_b b on a.pk = b.pk where a.pk is null a_pk a_value b_value b_pk ---- ---------- ---------- ---- null null microsoft 8 null null apple 9 null null scotch 11 (3 row(s) affected)

6、outer join：（外连接）

此联接也可以称为完全外联接或完全联接。此查询将返回两个表中的所有记录，连接左表(表a)中与右表(表b)中的记录相匹配的记录。此连接写成如下：

1 2 3 4 select <select_list> from table_a a full outer join table_b b on a. key = b. key

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 -- outer join select a.pk as a_pk, a.value as a_value, b.value as b_value, b.pk as b_pk from table_a a full outer join table_b b on a.pk = b.pk a_pk a_value b_value b_pk ---- ---------- ---------- ---- 1 fox trot 1 2 cop car 2 3 taxi cab 3 6 washington monument 6 7 dell pc 7 null null microsoft 8 null null apple 9 null null scotch 11 5 arizona null null 4 lincoln null null 10 lucent null null (11 row(s) affected)

7、outer excluding join：（外连接排除内连接结果）

此查询将返回左表(表a)中的所有记录和右表(表b)中不匹配的所有记录。我还不需要使用这种类型的联接，但所有其他类型的联接我都相当频繁地使用。此连接写成如下：

1 2 3 4 5 select <select_list> from table_a a full outer join table_b b on a. key = b. key where a. key is null or b. key is null

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 -- outer excluding join select a.pk as a_pk, a.value as a_value, b.value as b_value, b.pk as b_pk from table_a a full outer join table_b b on a.pk = b.pk where a.pk is null or b.pk is null a_pk a_value b_value b_pk ---- ---------- ---------- ---- null null microsoft 8 null null apple 9 null null scotch 11 5 arizona null null 4 lincoln null null 10 lucent null null (6 row(s) affected)

注意，在外部联接上，首先返回内部连接记录，然后返回右连接记录，最后返回左连接记录(至少，我的microsoft sql server就是这样做的；当然，这不需要使用任何orderby语句)。您可以访问维基百科文章以获得更多信息(但是，条目不是图形化的)。我还创建了一个备忘单，您可以在需要时打印出来。如果您右键单击下面的图像并选择“将目标保存为.”，您将下载完整大小的图像。