浅谈SQL的7种Join方式

SQL join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。Join 和 Key有时为了得到完整的结果，我们需要从两个或更多的表中获取结果。我们就需要执行 join。下面我们一起来看看SQL的 7种join方式。

 

数据库中的表可通过键将彼此联系起来。主键（Primary Key）是一个列，在这个列中的每一行的值都是唯一的。在表中，每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下，把表间的数据交叉捆绑在一起。

下面我们创建部门表tbl_dept和员工表tbl_emp对上述7种方式进行逐一实现：

部门表：主键id、部门名称deptName，部门楼层locAdd

mysql> CREATE TABLE `tbl_dept` (

    ->  `id` INT(11) NOT NULL AUTO_INCREMENT,

    ->  `deptName` VARCHAR(30) DEFAULT NULL,

    ->  `locAdd` VARCHAR(40) DEFAULT NULL,

    ->  PRIMARY KEY (`id`)

    -> ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

 

员工表：主键id，姓名name、所属部门deptId

 

mysql> CREATE TABLE `tbl_emp` (

    ->  `id` INT(11) NOT NULL AUTO_INCREMENT,

    ->  `name` VARCHAR(20) DEFAULT NULL,

    ->  `deptId` INT(11) DEFAULT NULL,

    ->  PRIMARY KEY (`id`),

    ->  KEY `fk_dept_id` (`deptId`)

    ->  #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `tbl_dept` (`id`)

    -> ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

 

然后插入一些测试数据：

mysql> INSERT INTO tbl_dept(deptName,locAdd) VALUES('技术部',11);

Query OK, 1 row affected (0.07 sec)

 

mysql> INSERT INTO tbl_dept(deptName,locAdd) VALUES('美工部',12);

Query OK, 1 row affected (0.08 sec)

 

mysql> INSERT INTO tbl_dept(deptName,locAdd) VALUES('总裁办',13);

Query OK, 1 row affected (0.06 sec)

 

mysql> INSERT INTO tbl_dept(deptName,locAdd) VALUES('人力资源',14);

Query OK, 1 row affected (0.11 sec)

 

mysql> INSERT INTO tbl_dept(deptName,locAdd) VALUES('后勤组',15);

Query OK, 1 row affected (0.10 sec)

 

mysql> insert into tbl_emp(name,deptId) values('jack',1);

Query OK, 1 row affected (0.11 sec)

 

mysql> insert into tbl_emp(name,deptId) values('tom',1);

Query OK, 1 row affected (0.08 sec)

 

mysql> insert into tbl_emp(name,deptId) values('alice',2);

Query OK, 1 row affected (0.08 sec)

 

mysql> insert into tbl_emp(name,deptId) values('john',3);

Query OK, 1 row affected (0.13 sec)

 

mysql> insert into tbl_emp(name,deptId) values('faker',4);

Query OK, 1 row affected (0.10 sec)

 

mysql> insert into tbl_emp(name) values('mlxg');

Query OK, 1 row affected (0.13 sec)

 

mysql> select * from tbl_dept;

+----+----------+--------+

| id | deptName | locAdd |

+----+----------+--------+

|  1 | 技术部   | 11     |

|  2 | 美工部   | 12     |

|  3 | 总裁办   | 13     |

|  4 | 人力资源 | 14     |

|  5 | 后勤组   | 15     |

+----+----------+--------+

5 rows in set (0.00 sec)

 

mysql> select * from tbl_emp;

+----+-------+--------+

| id | name  | deptId |

+----+-------+--------+

|  1 | jack  |      1 |

|  2 | tom   |      1 |

|  3 | alice |      2 |

|  4 | john  |      3 |

|  5 | faker |      4 |

|  7 | ning  |   NULL |

|  8 | mlxg  |   NULL |

+----+-------+--------+

7 rows in set (0.00 sec)

 

1、左连接（A独有+AB共有）

查询所有部门以及各部门的员工数：

mysql> select t1.id,t1.deptName,count(t2.name) as emps from tbl_dept t1 left join tbl_emp t2 on t2.deptId=t1.id group by deptName order by id;

+----+----------+------+

| id | deptName | emps |

+----+----------+------+

|  1 | 技术部   |    2 |

|  2 | 美工部   |    1 |

|  3 | 总裁办   |    1 |

|  4 | 人力资源 |    1 |

|  5 | 后勤组   |    0 |

+----+----------+------+

5 rows in set (0.00 sec)

 

2、右连接（B独有+AB共有）

查询所有员工及其所属部门：

mysql> select t2.id,t2.name,t1.deptName from tbl_dept t1 right join tbl_emp t2 on t2.deptId=t1.id;

+----+-------+----------+

| id | name  | deptName |

+----+-------+----------+

|  1 | jack  | 技术部   |

|  2 | tom   | 技术部   |

|  3 | alice | 美工部   |

|  4 | john  | 总裁办   |

|  5 | faker | 人力资源 |

|  7 | ning  | NULL     |

|  8 | mlxg  | NULL     |

+----+-------+----------+

7 rows in set (0.04 sec)

 

3、内连接（AB共有）

查询两表共有的数据：

mysql> select deptName,t2.name empName from tbl_dept t1 inner join tbl_emp t2 on t1.id=t2.deptId;

+----------+---------+

| deptName | empName |

+----------+---------+

| 技术部   | jack    |

| 技术部   | tom     |

| 美工部   | alice   |

| 总裁办   | john    |

| 人力资源 | faker   |

+----------+---------+

 

4、A独有

即在（A独有+AB共有）的基础之上排除B即可（通过b.id is null即可实现）：

mysql> select a.deptName,b.name empName from tbl_dept a left join tbl_emp b on a.id=b.deptId where b.id is null;

+----------+---------+

| deptName | empName |

+----------+---------+

| 后勤组   | NULL    |

+----------+---------+

 

5、B独有

与（A独有）同理：

mysql> select a.name empName,b.deptName from tbl_emp a left join tbl_dept b on a.deptId=b.id where b.id is null;

+---------+----------+

| empName | deptName |

+---------+----------+

| ning    | NULL     |

| mlxg    | NULL     |

+---------+----------+

 

6、A独有+B独有

使用union将（A独有）和（B独有）联合在一起：

mysql> select a.deptName,b.name empName from tbl_dept a left join tbl_emp b on a.id=b.deptId where b.id is null union select b.deptName,a.name emptName from tbl_emp a left join tbl_dept b on a.deptId=b.id where b.id is null;

+----------+---------+

| deptName | empName |

+----------+---------+

| 后勤组   | NULL    |

| NULL     | ning    |

| NULL     | mlxg    |

+----------+---------+

 

7、A独有+AB公共+B独有

使用union（可去重）联合（A独有+AB公共）和（B独有+AB公共）

mysql> select a.deptName,b.name empName  from tbl_dept a left join tbl_emp b on a.id=b.deptId union select a.deptName,b.name empName from tbl_dept a right join tbl_emp b on a.id=b.deptId;

+----------+---------+

| deptName | empName |

+----------+---------+

| 技术部   | jack    |

| 技术部   | tom     |

| 美工部   | alice   |

| 总裁办   | john    |

| 人力资源 | faker   |

| 后勤组   | NULL    |

| NULL     | ning    |

| NULL     | mlxg    |

+----------+---------+

 

以上就是SQL的7种Join方式及其实现，其主要目的还是从MySQL数据库的表中查询相关的数据，根据不同的数据需要采用不同的join方式。想要深入学习MySQL的小伙伴抓紧来本站的MySQL教程开始学习吧！