假设有员工和部门两张表
员工表EMP
员工 | 部门 |
---|---|
甲 | 人事 |
乙 | 采购 |
丙 | 无 |
部门表DEPT
部门 | 部门下员工 |
---|---|
人事 | 员工甲 |
采购 | 员工乙 |
销售 | 无员工 |
内连接:
#查询有部门的员工个人信息和他们的员工信息
SELECT * FROM EMP E INNER JOIN DEPT D ON E.Key = D.Key;
左连接:
#查询所有员工的个人信息和部门信息,人员没有所属部门的部门信息填空值
SELECT * FROM EMP E LEFT JOIN DEPT D ON E.id = D.id;
右连接:
#查询所有的部门信息和部门下的人员信息,部门下没有人员的此人员信息填空值
SELECT * FROM EMP E RIGHT JOIN DEPT D ON E.id = D.id;
左连接(不包含重叠):
#查询没有部门的员工的信息
SELECT * FROM EMP E LEFT JOIN DEPT D ON E.id = D.id WHERE D.id IS NULL;
右连接(不包含重叠):
#查询没有员工的部门的信息
SELECT * FROM EMP E LEFT JOIN DEPT D ON E.id = D.id WHERE E.id IS NULL;
全连接:
#显示出公司的人事架构,公司的各个部门和公司下的所有员工,无论部门下有没有人或者员工有没有所属部门
SELECT * FROM EMP E FULL OUTER JOIN DEPT D ON E.id = D.id;
#在mysql中并不支持FULL OUTER JOIN,所以使用左连接和右连接查询后再去重来实现
SELECT * FROM EMP E LEFT JOIN DEPT D ON E.id = D.id UNION
SELECT * FROM EMP E RIGHT JOIN DEPT D ON E.id = D.id;
显示两表的独有的数据:
#查处没有所属部门的员工和没有下属员工的部门
SELECT * FROM EMP E LEFT JOIN DEPT D ON E.id = D.id WHERE D.id IS NULL UNION
SELECT * FROM EMP E LEFT JOIN DEPT D ON E.id = D.id WHERE E.id IS NULL;
拓展:
MYSQL NATURAL JOIN
在MySQL中,NATURAL JOIN是一种特殊的连接类型,它根据两个表中具有相同名称和类型的列自动进行连接。你不需要明确指定连接条件,因为NATURAL JOIN会自动找到这些匹配的列。当你使用NATURAL JOIN时,MySQL会返回两个表中所有列的组合。
如在员工表中有字段deptid,部门表的id字段名也叫deptid
那么使用
SELECT * FROM EMP E NATURAL JOIN DEPT D;
等同于
SELECT * FROM EMP E INNER JOIN DEPT D ON E.deptid = D.deptid;
在实际使用中,你应该谨慎使用NATURAL JOIN,因为它依赖于列名称的匹配,这可能会导致意外的连接条件。如果表结构发生变化,比如添加了新的列或者列名称发生了变化,这可能会影响到NATURAL JOIN的结果。如果两个表中没有相同名称和类型的列,那么NATURAL JOIN将产生一个笛卡尔积,即左表中的每一行与右表中的每一行组合。
与之相似的还有一种Using连接
SELECT * FROM EMP E JOIN DEPT D USING (`deptid`);
使用它要确保两个表内都有这个名字的字段,否则会报错。