一、join圖

image.png

二、sql語句準備

CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
empno int not null,
PRIMARY KEY (`id`),
KEY `idx_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

# 數據
INSERT INTO t_dept(deptName,address) VALUES('華山','華山');
INSERT INTO t_dept(deptName,address) VALUES('丐幫','洛陽');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武當','武當山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明頂');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');

INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('風清揚',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐沖',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('喬峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('滅絕師太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('張三豐',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('張無忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韋小寶',18,null,100010);

部門表數據

部門表

員工表

三、七種連接方式詳解

1、內連接 inner join

內連接inner join表示A表和B表的共有部分數據。

內連接 inner join

select * from t_emp e INNER JOIN t_dept d on e.deptId = d.id;

inner join

2、左連接 left join

左連接 left join 表示A表和B表的公共部分，再加上A表的獨有部分。

左連接 left join

select * from t_emp e left JOIN t_dept d on e.deptId = d.id;

left join

3、右連接 right join

右連接right join 表示A表和B表公共部分，在加上B表的獨有部分。

右連接right join

select * from t_emp e right JOIN t_dept d on e.deptId = d.id;

right join

4、A表獨有

查找A表獨有部分，則需查找A表和B表的共有部分并加上A表的獨有部分，在將A表和B表的共有部分剔除即可(也就是挑選B的主鍵為空的數據)。

A表獨有

select * from t_emp e left JOIN t_dept d on e.deptId = d.id where d.id is null;

A表獨有部分

5、B表獨有

查找B表獨有部分，則需查找A表和B表的共有部分并加上B表的獨有部分，在將A表和B表的共有部分剔除即可(也就是挑選A的主鍵為空的數據)。

B表獨有

select * from t_emp e right JOIN t_dept d on e.deptId = d.id where e.deptId is null;

B表獨有

6、全連接full outer join

全連接則表示將A表和B表的公共部分及A表、B表的獨有部分，所有數據都查詢出來

全連接full outer join

指導圖的全連接 full outer join 在mysql 語法報錯！但是可以通過union關鍵字進行查詢。
UNION會把 重復的行去掉，返回的行都是唯一的。如果想保留重復行，可以使用 UNION ALL 關鍵字。
UNION其實就是將A表和B表的共有部分及A表的獨有部分(即左連接left join)加上A、B表共有部分及B表的獨有部分(即右連接right join)合并起來，并進行去重即可。

select * from t_emp e left join t_dept d on e.deptId = d.id
union
select * from t_emp e right join t_dept d on e.deptId = d.id;

全連接full outer join

7、A表獨有和B表獨有

查詢A表獨有部分并加上B表獨有部分

A表獨有和B表獨有

實際就是查詢A表的獨有部分和B表的獨有部分，使用UNION進行連接即可。

select * from t_emp e left join t_dept d on e.deptId = d.id where d.id is null
union
select * from t_emp e right join t_dept d on e.deptId = d.id where e.deptId is null;

A表獨有和B表獨有