MySQL 表连接 JOIN

参考

前言

建表语句

表a

CREATE TABLE `a_table` (
  `a_id` int(11) DEFAULT NULL,
  `a_name` varchar(10) DEFAULT NULL,
  `a_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

表b

CREATE TABLE `b_table` (
  `b_id` int(11) DEFAULT NULL,
  `b_name` varchar(10) DEFAULT NULL,
  `b_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

测试数据

表a

mysql> SELECT * FROM table_a;
+------+--------+-----------+
| a_id | a_name | a_part    |
+------+--------+-----------+
|    1 | 老潘   | 总裁部    |
|    2 | 老王   | 秘书部    |
|    3 | 老张   | 设计部    |
|    4 | 老李   | 运营部    |
+------+--------+-----------+

表b

mysql> SELECT * FROM table_b;
+------+--------+-----------+
| b_id | b_name | b_part    |
+------+--------+-----------+
|    2 | 老王   | 秘书部    |
|    3 | 老张   | 设计部    |
|    5 | 老刘   | 人事部    |
|    6 | 老黄   | 生产部    |
+------+--------+-----------+

表a 和 表b内连接
也就是笛卡尔积
第一个表的每一行都与第二个表中的没每一行连接

mysql> select * from table_a inner join table_b;
+------+--------+-----------+------+--------+-----------+
| a_id | a_name | a_part    | b_id | b_name | b_part    |
+------+--------+-----------+------+--------+-----------+
|    1 | 老潘   | 总裁部    |    2 | 老王   | 秘书部    |
|    2 | 老王   | 秘书部    |    2 | 老王   | 秘书部    |
|    3 | 老张   | 设计部    |    2 | 老王   | 秘书部    |
|    4 | 老李   | 运营部    |    2 | 老王   | 秘书部    |
|    1 | 老潘   | 总裁部    |    3 | 老张   | 设计部    |
|    2 | 老王   | 秘书部    |    3 | 老张   | 设计部    |
|    3 | 老张   | 设计部    |    3 | 老张   | 设计部    |
|    4 | 老李   | 运营部    |    3 | 老张   | 设计部    |
|    1 | 老潘   | 总裁部    |    5 | 老刘   | 人事部    |
|    2 | 老王   | 秘书部    |    5 | 老刘   | 人事部    |
|    3 | 老张   | 设计部    |    5 | 老刘   | 人事部    |
|    4 | 老李   | 运营部    |    5 | 老刘   | 人事部    |
|    1 | 老潘   | 总裁部    |    6 | 老黄   | 生产部    |
|    2 | 老王   | 秘书部    |    6 | 老黄   | 生产部    |
|    3 | 老张   | 设计部    |    6 | 老黄   | 生产部    |
|    4 | 老李   | 运营部    |    6 | 老黄   | 生产部    |
+------+--------+-----------+------+--------+-----------+
16 rows in set (0.00 sec)

一、表连接JOIN基础

1、什么是表连接、什么是JOIN

• 当需要同时显示多个表中的字段时, 就可以用表连接来实现这样的功能.
• SQL 中使用 JOIN 字段来进行表连接.
• SQL JOIN 用于根据两个或多个表中的列之间的关系, 从这些表中查询数据

2、表连接的分类

2.1、内连接

什么是内连接
内连接是指连接结果仅包含符合条件的行, 参与连接的两(或多)个表都应该符合条件.
内连接的语法

SELECT * FROM table_a INNER JOIN table_b ON condition
SELECT * FROM table_a, tabke_b where condition

• INNER JOIN 前后为要连接的表, 如果没有ON条件, 就是笛卡尔积的结果
• INNER JOIN 和JOIN 相同
• ON 后面为筛选条件
• 第二句没有连接条件的情况下和第一句在语意上等效
• 然而, 逗号运算符优先级低于 INNER JOIN, CROSS JOIN, LEFT JOIN等等. 如果在存在连接条件时逗号连接与其它连接类型混合使用, 则可能会出现形式错误. 之后讲解

Unknown column 'col_name' in 'on clause'

2.2、外连接

什么是外连接
连接结果不仅包含符合连接条件的行同时也会包含自身不符合条件的行. 包括左连外连接、有外连接和全外连接.

外连接分类

• 左连接(左外连接) 关键字 LEFT JOIN ON/ LEFT OUTER JOIN ON
• 右连接(右外连接) 关键字 RIGHT JOIN ON/RIGHT OUTER JOIN ON
• 全外连接 MYSQL 不支持, 可以使用其它方式替换解决

左连接语句
左连接将左表的记录全部表示出来, 而右表只会显示符合搜索条件的记录. 右表记录不足的的地方均为NULL

SELECT * FROM table_a LEFT JOIN table_b ON table_a.a_id = table_b.b_id;

结果

+------+--------+-----------+------+--------+-----------+
| a_id | a_name | a_part    | b_id | b_name | b_part    |
+------+--------+-----------+------+--------+-----------+
|    2 | 老王    | 秘书部     |    2 | 老王   | 秘书部    |
|    3 | 老张    | 设计部     |    3 | 老张   | 设计部    |
|    1 | 老潘    | 总裁部     | NULL | NULL  | NULL      |
|    4 | 老李    | 运营部     | NULL | NULL  | NULL      |
+------+--------+-----------+------+--------+-----------+

右连接语句
右连接将右表的纪录全部显示出来, 而左表只会显示符合搜素条件的记录. 左表记录不足的地方均为NULL

SELECT * FROM table_a RIGHT JOIN table_b ON table_a.a_id = table_b.b_id;

结果

+------+--------+-----------+------+--------+-----------+
| a_id | a_name | a_part    | b_id | b_name | b_part    |
+------+--------+-----------+------+--------+-----------+
|    2 | 老王    | 秘书部     |    2 | 老王   | 秘书部    |
|    3 | 老张    | 设计部     |    3 | 老张   | 设计部    |
| NULL | NULL   | NULL      |    5 | 老刘   | 人事部    |
| NULL | NULL   | NULL      |    6 | 老黄   | 生产部    |
+------+--------+-----------+------+--------+-----------+

MYSQL全连接的实现
返回左右表匹配的行, 并且返回左表不足的地方为NULL, 右表不足的地方为NULL, 其实就是左连接和右连接的集合

SELECT * FROM table_a LEFT JOIN table_b ON table_a.a_id = table_b.b_id UNION
SELECT * FROM table_a RIGHT JOIN table_b ON table_a.a_id = table_b.b_id;

结果

+------+--------+-----------+------+--------+-----------+
| a_id | a_name | a_part    | b_id | b_name | b_part    |
+------+--------+-----------+------+--------+-----------+
|    2 | 老王   | 秘书部      |    2 | 老王   | 秘书部    |
|    3 | 老张   | 设计部      |    3 | 老张   | 设计部    |
|    1 | 老潘   | 总裁部      | NULL | NULL   | NULL    |
|    4 | 老李   | 运营部      | NULL | NULL   | NULL    |
| NULL | NULL   | NULL      |    5 | 老刘   | 人事部    |
| NULL | NULL   | NULL      |    6 | 老黄   | 生产部    |
+------+--------+-----------+------+--------+-----------+

2.3、等值连接

什么是等值连接
在连接条件中使用等于号(=)运算符比较被连接的列, 其查询结果中列出被连接表中的所有列, 包括其中重复列.
等值连接是内连接的子集, 等级连接是特殊的内连接, 条件使用等号的内连接
等值连接语句

SELECT * FROM table_a INNER JOIN table_b ON table_a.a_id = table_b.b.id where table_a.a_id = table_b.b_id;
SELECT * FROM table_a , table_b where table_a.a_id = table_b.b_id;

结果

+------+--------+-----------+------+--------+-----------+
| a_id | a_name   | a_part    | b_id | b_name   | b_part    |
+------+--------+-----------+------+--------+-----------+
|    2 | 老王    | 秘书部     |    2 | 老王   | 秘书部     |
|    3 | 老张    | 设计部     |    3 | 老张   | 设计部     |
+------+--------+-----------+------+--------+-----------+

2.4、自然连接

什么是自然连接
自然连接是一种特殊的等值连接, 他要求两个关系表中进行连接的必须是相同的属性列(名字相同), 无须添加连接条件, 并且在结果中消除重复的属性列.
所有相同名称的列的内容相同才会有结果
自然连接关键字

... NATURAL JOIN ...

自然连接语句
最上面两个表进行自然连接的结果为空, 因为没有相同名称的列, 将两个表的a_name、b_name变为name 可以进行自然连接

SELECT * FROM table_a NATURAL JOIN table_b

结果

+--------+------+-----------+------+-----------+
| name   | a_id | a_part    | b_id | b_part    |
+--------+------+-----------+------+-----------+
| 老王    |    2 | 秘书部     |    2 | 秘书部    |
| 老张    |    3 | 设计部     |    3 | 设计部    |
+--------+------+-----------+------+-----------+