MySQL 表连接（外连接）

# 外连接
# 左连接
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