MySQL：多表查询

SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。查询多张表的语法是：SELECT * FROM <表1> <表2>，普通多表查询会获取M x N行记录，所以一般使用连接查询或子查询获取多张表的数据；

连接查询

连接查询对多个表进行 JOIN 运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

连接查询分为内连接和外连接，内连接只返回同时存在于两张表的行数据，外连接返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。

内连接

内连接是最常用的一种JOIN查询，内连接查询的语法格式：

# 标准语法，INNER可省略
SELECT ... FROM 表1  JOIN 表2 ON 连接条件 ;

# 衍生语法1（WHERE与ON的作用相同）
SELECT ... FROM 表1 JOIN 表2 WHERE 连接条件 ;

# 衍生语法2
SELECT ... FROM 表1 , 表2 WHERE 连接条件 ;

查询实例：

# 连接员工表与部门表两张表
SELECT e.empno,e.ename,d.dname
FROM t_emp e
JOIN t_dept d ON e.deptno=d.deptno ;

# 连接员工表、部门表、登记表三张表
SELECT e.empno,e.ename,d.dname,e.sal,e.job,s.grade
FROM t_emp e
JOIN t_dept d ON e.deptno=d.deptno 
JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

# 连接自身：查询与SCOTT同部门的人
SELECT e2.ename
FROM t_emp e1
JOIN t_emp e2 ON e1.deptno=e2.deptno
WHERE e1.ename="SCOTT" AND e2.ename!="SCOTT";

# 进阶练习1：查询员工表中工资超过平均工资的人
# 与WHERE一样，ON子句也不能使用聚合函数，这里将聚合结果作为一张表来连接
SELECT e.ename,e.sal
FROM t_emp e
JOIN (SELECT AVG(sal) avg FROM t_emp) t ON e.sal>=t.avg;

内连接的数据表不一定需要同名字段或外键关联，只需字段之间符合逻辑关系即可

外连接

由于内连接只返回同时存在于两张表的行数据，如果员工表中有部门编号为NULL的特殊员工，使用内连接就会遗漏这个员工的信息，这时候就需要使用外连接，外连接分为LEFT [OUTER] JOIN、RIGHT [OUTER] JOIN、FULL [OUTER] JOIN，左右相对JOIN关键字前后的表而言的：

左连接实例1：查询所有员工（包括部门为NULL）的部门信息

SELECT e.ename,d.dname
FROM t_emp e 
LEFT JOIN t_dept d ON e.deptno=d.deptno;

左连接实例2：查询所有部门的人数

SELECT d.dname,COUNT(e.deptno)
FROM t_dept d 
LEFT JOIN t_emp e ON d.deptno=e.deptno
GROUP BY d.deptno;

MySQL数据库不支持全连接查询，可使用UNION关键字实现全连接：

(SELECT d.dname,COUNT(e.deptno)
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);

内连接中，ON与WHERE的用法一样；外连接中不太一样，WHERE能筛选掉更多数据。

子查询

根据所在位置，子查询可分为WHERE、FROM、SELECT子查询。对于WHERE、SELECT子查询，每次比较都会运行一次，非常低效，不推荐使用，一般转化为表连接查询。对于FROM子查询只会执行一次，可以经常使用。

根据子查询的返回结果，可以分为单行和多行子查询，单行子查询即子查询返回一个值，很容易使用。对于多行子查询，可以使用IN、ALL、ANY、[NOT] EXISTS关键字来处理