MySQL 的表连接（有案例）

1、

案例：
已知2张基本表：部门表：dept （部门号，部门名称）;员工表 emp（员工号，员工姓名，年龄，入职时间，收入，部门号）
CREATE table dept(dept1 VARCHAR(6),dept_name VARCHAR(20)) default charset=utf8;
INSERT into dept VALUES ('101','财务');
INSERT into dept VALUES ('102','销售');
INSERT into dept VALUES ('103','IT技术');
INSERT into dept VALUES ('104','行政');
CREATE table emp (sid VARCHAR(6),name VARCHAR(20),age TINYINT(2),woektime_start VARCHAR(10),incoming SMALLINT(10),dept2 VARCHAR(6))default charset=utf8;

insert into emp VALUES ('1789','张三',35,'1980/1/1',4000,'101');
insert into emp VALUES ('1674','李四',32,'1983/4/1',3500,'101');
insert into emp VALUES ('1776','王五',24,'1990/7/1',2000,'101');
insert into emp VALUES ('1568','赵六',57,'1970/10/11',7500,'102');
insert into emp VALUES ('1564','荣七',64,'1963/10/11',8500,'102');
insert into emp VALUES ('1879','牛八',55,'1971/10/20',7300,'103');
insert into emp VALUES ('1880','老九',55,'1971/10/20',7500,'105');
insert into emp VALUES ('1900','老十',64,'1990/8/1',2000,'106');

select * from dept;
select * from emp ;




--内连接
select * from dept INNER JOIN emp on dept.dept1=emp.dept2
--隐藏连接
select * from dept,emp where dept.dept1=emp.dept2

--左连接(左表没连接的就是null-看右边）
select * from dept LEFT JOIN emp on dept.dept1=emp.dept2

--右连接（右表没连接的就是null-看左边）
select * from dept RIGHT JOIN emp on dept.dept1=emp.dept2

--左独有(左表中才有的）
select * from dept LEFT JOIN emp on dept.dept1=emp.dept2
WHERE name is NULL

--右独有(右表中才有的）
select * from dept RIGHT JOIN emp on dept.dept1=emp.dept2
WHERE dept1 is NULL

--全连接
（1）内连接+左独有+右独有（中间加union)
select * from dept INNER JOIN emp on dept.dept1=emp.dept2
UNION
select * from dept LEFT JOIN emp on dept.dept1=emp.dept2
WHERE name is NULL
UNION
select * from dept RIGHT JOIN emp on dept.dept1=emp.dept2
WHERE dept1 is NULL

--(2)左连接+右独有
SELECT * from dept LEFT JOIN emp on dept.dept1=emp.dept2
UNION
select * from dept RIGHT JOIN emp on dept.dept1=emp.dept2
WHERE dept1 is NULL

--（3）右连接+左独有
SELECT * from dept RIGHT JOIN emp on dept.dept1=emp.dept2
UNION
select * from dept LEFT JOIN emp on dept.dept1=emp.dept2
WHERE name is NULL