# 外连接
# 左连接
SELECT e.ename,e.deptno
FROM t_emp e LEFT JOIN t_dept d
ON e.deptno=d.deptno;
# 右连接
SELECT e.ename,e.deptno
FROM t_dept d RIGHT JOIN t_emp e
ON e.deptno=d.deptno;
# 综合练习
# 查询每个部门的名称和部门人数
SELECT d.dname,count(e.deptno)
FROM t_dept d LEFT JOIN t_emp e
ON e.deptno = d.deptno
GROUP BY d.deptno;
# 查询每个部门的名称和部门的人数,如果没有部门的员工,部门名称用null代替
# UNION关键字可以将多个查询语句的结果集进行合并
(SELECT d.dname,COUNT(e.ename)
FROM t_dept d LEFT JOIN t_emp e
ON d.deptno = e.deptno
GROUP BY d.deptno
)
UNION
(SELECT d.dname,COUNT(*)
FROM t_dept d RIGHT JOIN t_emp e
ON d.deptno = e.deptno
GROUP BY d.deptno
)
# 综合实例
# 查询每名员工的编号、姓名、部门、月薪、工资等级、工龄、上司编号、上司名称、上司部门
# 获取各表中的字段数据
SELECT
e.empno as "编号",
e.ename as "姓名",
d.dname as "部门",
e.sal+IFNULL(e.comm,0) as "月薪",
s.grade as "工资等级",
FLOOR(DATEDIFF(NOW(),e.hiredate)/365) as "工龄",
t.empno as "上司编号",
t.ename as "上司名称",
t.dname as "上司部门"
FROM t_emp e LEFT JOIN t_dept d
ON e.deptno=d.deptno # 条件:如果e表中的部门编号等于t表中的部门编号则显示
LEFT JOIN t_salgrade s
ON e.sal BETWEEN s.losal AND s.hisal # 条件:如果e表中的底薪在s表中价格范围则显示
LEFT JOIN
(
# 找出上司的员工列表
SELECT e1.empno,e1.ename,d1.dname
FROM t_emp e1
JOIN t_dept d1
ON e1.deptno=d1.deptno # 条件:如果e表中的部门编号等于t表中的部门编号则显示
) t
ON e.mgr = t.empno # 条件:如果e表中的上司编号等于t表中的员工编号则显示
# 内连接只保留复合条件的记录,所以查询条件写在on子句和where子句的效果是相同的,
# 但是外连接里面,条件写在where子句里面,不符合条件的记录是会被过滤掉的。
-- 对比1
SELECT e.ename,d.dname,d.deptno
FROM t_emp e left JOIN t_dept d
ON e.deptno = d.deptno AND e.deptno = 10
-- 对比1
SELECT e.ename,d.dname,d.deptno
FROM t_emp e left JOIN t_dept d
ON e.deptno = d.deptno WHERE e.deptno = 10