sql中的7中join
一、7种常见的JOIN
-
INNER JOIN
-
LEFT JOIN
-
RIGHT JOIN
-
左的独有
select * from TableA A
left join TableB B on A.Key = B.Key
where B.Key is null;
- 右的独有
SELECT * FROM TableA A
RIGHT JOIN TableB B ON A.Key = B.Key
WHERE A.Key IS NULL;
- 全外连接
SELECT * FROM TableA A
FULL OUTER TableB B
ON A.Key = B.Key
- 除去共有部分
SELECT * FROM TableA A
FULL OUTER TableB B ON A.Key = B.Key
WHERE A.Key IS NULL
OR B.Key IS NULL;
二、建表
#建库
CREATE DATABASE employees;
USE employees;
#建表
CREATE TABLE tbl_dept(
id INT PRIMARY KEY AUTO_INCREMENT,
deptName VARCHAR(30) DEFAULT NULL,
IocAdd VARCHAR(40) DEFAULT NULL
);
CREATE TABLE tbl_emp(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20),
deptId INT
#CONSTRAINT fk_dept_id FOREIGN KEY (deptid) REFERENCES tbl_dept(id)
)
INSERT INTO tbl_dept(deptname,iocadd) VALUES('RD',11);
INSERT INTO tbl_dept(deptname,iocadd) VALUES('HR',12);
INSERT INTO tbl_dept(deptname,iocadd) VALUES('MK',13);
INSERT INTO tbl_dept(deptname,iocadd) VALUES('MIS',14);
INSERT INTO tbl_dept(deptname,iocadd) 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);
三、测试
- INNER JOIN
SELECT * FROM `tbl_emp` a
INNER JOIN `tbl_dept` b ON a.`deptId` = b.`id`;
- LEFT JOIN
SELECT * FROM `tbl_emp` a
LEFT JOIN `tbl_dept` b ON a.`deptId` = b.`id`;
- RIGHT JOIN
SELECT * FROM `tbl_emp` a
RIGHT JOIN `tbl_dept` b ON a.`deptId` = b.`id`;
- 左的独有
SELECT * FROM `tbl_emp` a
LEFT JOIN `tbl_dept` b ON a.`deptId` = b.`id`
WHERE b.`id` IS NULL;
- 右的独有
SELECT * FROM `tbl_emp` a
RIGHT JOIN `tbl_dept` b ON a.`deptId` = b.`id`
WHERE a.`deptId` IS NULL;
- 全外连接
SELECT * FROM `tbl_emp` a
FULL OUTER `tbl_dept` b ON a.`deptId` = b.`id`;
#改良写法
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`;
- 除去共有部分
SELECT * FROM `tbl_emp` a
FULL OUTER `tbl_dept` b ON a.`deptId` = b.`id`
WHERE a.`deptId` IS NULL
OR b.`id` IS NULL;
#改良写法
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;