首先创建测试数据库，以及相关表，以员工表，部门表为例。
CREATE DATABASE IF NOT EXISTS test_join; -- 部门表 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; -- 员工表 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_table_id` (`deptId`) )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; -- 插入数据 INSERT INTO tbl_dept(deptName,locAdd) VALUES ('RD',11), ('HR',12), ('MK',13), ('MIS',14), ('FD',15); INSERT INTO tbl_emp(`name`,deptId) VALUES ('z3',1), ('z3',1), ('z4',1), ('w5',2), ('w6',2), ('s7',3), ('s8',4), ('s9',51);
创建完表之后，首先显示表数据。
mysql> select * from tbl_emp;
+----+------+--------+
| id | name | deptId |
+----+------+--------+
| 1 | z3 | 1 |
| 2 | z3 | 1 |
| 3 | z4 | 1 |
| 4 | w5 | 2 |
| 5 | w6 | 2 |
| 6 | s7 | 3 |
| 7 | s8 | 4 |
| 8 | s9 | 51 |
+----+------+--------+
mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
| 1 | RD | 11 |
| 2 | HR | 12 |
| 3 | MK | 13 |
| 4 | MIS | 14 |
| 5 | FD | 15 |
+----+----------+--------+
开始测试
inner join
mysql> select * from tbl_emp a inner join tbl_dept b on a.deptid = b.id ;
+----+------+--------+----+----------+--------+
| id | name | deptId | id | deptName | locAdd |
+----+------+--------+----+----------+--------+
| 1 | z3 | 1 | 1 | RD | 11 |
| 2 | z3 | 1 | 1 | RD | 11 |
| 3 | z4 | 1 | 1 | RD | 11 |
| 4 | w5 | 2 | 2 | HR | 12 |
| 5 | w6 | 2 | 2 | HR | 12 |
| 6 | s7 | 3 | 3 | MK | 13 |
| 7 | s8 | 4 | 4 | MIS | 14 |
+----+------+--------+----+----------+--------+
inner join查询两表的交集部分，由于s9的部门名为51，而在部门表中并没有51号部门，所有查不出s9员工的相关信息，部门表中的5号部门同理。
left join -- right join
mysql> select * from tbl_emp a left join tbl_dept b on a.deptid = b.id ;
+----+------+--------+------+----------+--------+
| id | name | deptId | id | deptName | locAdd |
+----+------+--------+------+----------+--------+
| 1 | z3 | 1 | 1 | RD | 11 |
| 2 | z3 | 1 | 1 | RD | 11 |
| 3 | z4 | 1 | 1 | RD | 11 |
| 4 | w5 | 2 | 2 | HR | 12 |
| 5 | w6 | 2 | 2 | HR | 12 |
| 6 | s7 | 3 | 3 | MK | 13 |
| 7 | s8 | 4 | 4 | MIS | 14 |
| 8 | s9 | 51 | NULL | NULL | NULL |
+----+------+--------+------+----------+--------+
查询范围相比inner join的范围要大，查出了两表中相交部分，由于是left join，则a表中的所有数据都会被查出来，而b表中的数据用null填充，因为b表中没有id为51的部门。
mysql> select * from tbl_emp a right join tbl_dept b on a.deptid = b.id ;
+------+------+--------+----+----------+--------+
| id | name | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
| 1 | z3 | 1 | 1 | RD | 11 |
| 2 | z3 | 1 | 1 | RD | 11 |
| 3 | z4 | 1 | 1 | RD | 11 |
| 4 | w5 | 2 | 2 | HR | 12 |
| 5 | w6 | 2 | 2 | HR | 12 |
| 6 | s7 | 3 | 3 | MK | 13 |
| 7 | s8 | 4 | 4 | MIS | 14 |
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+----+----------+--------+
作用于left join正好相反。
left join + where 条件 -- right join + where条件
mysql> select * from tbl_emp a left join tbl_dept b on a.deptid = b.id where b.id is null;
+----+------+--------+------+----------+--------+
| id | name | deptId | id | deptName | locAdd |
+----+------+--------+------+----------+--------+
| 8 | s9 | 51 | NULL | NULL | NULL |
+----+------+--------+------+----------+--------+
查出a表中独有的部门id。
mysql> select * from tbl_emp a right join tbl_dept b on a.deptid = b.id where a.id is null;
+------+------+--------+----+----------+--------+
| id | name | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+----+----------+--------+
1 row in set (0.00 sec)
查出b表中独有的部分。
full outer join
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id union select * from tbl_emp a right join tbl_dept b on a.deptIdd = b.id;
+------+------+--------+------+----------+--------+
| id | name | deptId | id | deptName | locAdd |
+------+------+--------+------+----------+--------+
| 1 | z3 | 1 | 1 | RD | 11 |
| 2 | z3 | 1 | 1 | RD | 11 |
| 3 | z4 | 1 | 1 | RD | 11 |
| 4 | w5 | 2 | 2 | HR | 12 |
| 5 | w6 | 2 | 2 | HR | 12 |
| 6 | s7 | 3 | 3 | MK | 13 |
| 7 | s8 | 4 | 4 | MIS | 14 |
| 8 | s9 | 51 | NULL | NULL | NULL |
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+------+----------+--------+
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null union select * from tbl_emp a right join tbll_dept b on a.deptId = b.id where a.deptId is null ;
+------+------+--------+------+----------+--------+
| id | name | deptId | id | deptName | locAdd |
+------+------+--------+------+----------+--------+
| 8 | s9 | 51 | NULL | NULL | NULL |
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+------+----------+--------+
查出a表的独有和b表的独有部分。