一文让你彻底理解SQL中的join

1.常见的7种join

SQL join 用于把来自两个或多个表的行结合起来。

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法，通过韦恩图易于理解且易于记忆。

2.MySQL中的Join实践

2.1 数据准备

-- 建表语句
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_dept_id`(`deptId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- 插入数据
INSERT INTO tbl_dept (deptName, locAdd) VALUES ('RD', '11');
INSERT INTO tbl_dept (deptName, locAdd) VALUES ('HR', '12');
INSERT INTO tbl_dept (deptName, locAdd) VALUES ('MK', '13');
INSERT INTO tbl_dept (deptName, locAdd) VALUES ('MIS', '14');
INSERT INTO tbl_dept (deptName, locAdd) VALUES ('FD', '15');


INSERT INTO tbl_emp (name, deptId) VALUES ('z3', '1');
INSERT INTO tbl_emp (name, deptId) VALUES ('z4', '1');
INSERT INTO tbl_emp (name, deptId) VALUES ('z5', '1');
INSERT INTO tbl_emp (name, deptId) VALUES ('w5', '2');
INSERT INTO tbl_emp (name, deptId) VALUES ('w6', '2');
INSERT INTO tbl_emp (name, deptId) VALUES ('s7', '3');
INSERT INTO tbl_emp (name, deptId) VALUES ('s8', '4');
INSERT INTO tbl_emp (name, deptId) VALUES ('s9', '51');

# 确认数据插入完成
SELECT * FROM `tbl_emp`;
SELECT * FROM `tbl_dept`;

2.2 Join-inner join

# inner join 结果显示a表和b表共有的部分
SELECT * FROM tbl_emp  a INNER JOIN tbl_dept b ON a.deptId = b.id; 

2.3 Join-left join

# left join 结果显示a表和b表共有的部分+a表独有的部分
SELECT * FROM tbl_emp  a LEFT JOIN tbl_dept b ON a.deptId = b.id; 

2.4 Join-right join

# right join 结果显示a表和b表共有的部分+b表独有的部分
SELECT * FROM tbl_emp  a RIGHT JOIN tbl_dept b ON a.deptId = b.id; 

2.5 Join-left join + where

# left join + where  结果显示为a独有部分，与left join的区别在于去掉了a表与b表的共有部分
SELECT * FROM tbl_emp  a LEFT JOIN tbl_dept b ON a.deptId = b.id WHERE b.id IS NULL; 

2.6 Join-right join + where

# right join + where 结果显示为b独有部分，与left join的区别在于去掉了a表与b表的共有部分
SELECT * FROM tbl_emp  a RIGHT JOIN tbl_dept b ON a.deptId = b.id WHERE a.deptId IS NULL; 

2.7 Join-full outer join

# full outer join 结果显示：a表与b表共有部分 + a表独有部分 + b表独有部分
SELECT * FROM tbl_emp  a FULL OUTER JOIN tbl_dept b ON a.deptId = b.id; 
# mysql不支持full outer join语法 oracle支持此语法

# 解决办法:union(合并+去重)
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.deptId = b.id; 

2.8 Join-full outer join+where

# full outer join + where
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 tbl_dept b ON a.deptId = b.id WHERE a.deptId IS NULL;