彻底理解SQL中的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_dept_id`(`deptId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- 插入数据
INSERT INTO tbl_dept (deptName, locAdd) VALUES ('工程部', '11');
INSERT INTO tbl_dept (deptName, locAdd) VALUES ('财务部', '12');
INSERT INTO tbl_dept (deptName, locAdd) VALUES ('开发部', '13');
INSERT INTO tbl_dept (deptName, locAdd) VALUES ('营销部', '14');
INSERT INTO tbl_dept (deptName, locAdd) VALUES ('人事部', '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`;

1、 inner join

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

 

 2、left join

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

 

3、right join

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

 

4、left join + where 

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

5、right join + where

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

6、full outer join

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

# 解决办法:union(合并+去重)
SELECT * FROM tbl_emp  a LEFT JOIN tbl_dept b ON  b.id = a.deptId
UNION 
SELECT * FROM tbl_emp  a RIGHT JOIN tbl_dept b ON b.id = a.deptId

7、full outer join+where

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

SELECT * FROM tbl_emp  a LEFT JOIN tbl_dept b ON  b.id = a.deptId WHERE b.id IS NULL
UNION
SELECT * FROM tbl_emp  a RIGHT JOIN tbl_dept b ON b.id = a.deptId WHERE a.id  IS NULL