8. sql中的7中join

CAFEBABE 34

已于 2022-05-02 17:43:25 修改

阅读量574

点赞数 1

分类专栏：数据库文章标签：开发语言后端 mysql sql

于 2021-12-01 16:17:52 首次发布

本文链接：https://blog.csdn.net/hznb_369/article/details/121656971

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

sql中的7中join

一、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;