10、多表数据查询----第2篇

小结：
建议不使用连接查询，使用子查询。
一个SELECT得到的就是一个表。
多表连接时应该把每个表先瘦身，之后再连接。
给（子查询）表或者列重命名为简短的代号。

对应第11章

连接查询分为内连接和外连接。
一般多表查询时不使用连接查询，效率低。可以用子查询。
内连接（INNER JOIN）：只保留多张表匹配的数据部分，不匹配的不保留。
外连接（OUTER JOIN）：分为左外连接、右外连接、全外连接。其中左外连接除了保留匹配的还保留左表中不匹配的数据，右外连接除了保留匹配的还保留右表中不匹配的数据，全外连接除了保留匹配的还保留两表中不匹配的数据部分。

1、内连接

小技巧：表和自己的自连接，给表重命名一个短名字

语法：
SELECT ....
    FROM table1 INNER JOIN table2 [INNER JOIN table3....]
    ON 连接的条件;

例：
-- 自连接
-- 自连接，给表起短名e和l
SELECT e.ename employeename,e.job,l.ename loadername
    FROM t_employee e INNER JOIN t_employee l
    ON e.mgr=l.empno;

-- 多个内连接
SELECT e.ename employeename,d.dname,d.loc
    FROM t_employee e INNER JOIN t_employee l ON e.mgr=l.empno
                      INNER JOIN t_dept d ON l.deptno=d.deptno;

2、外连接

分为：左、右、全连接

语法：
SELECT ....
    FROM TABLE1 LEFT|RIGHT|FULL JOIN TABLE2
    ON 条件;

例：
SELECT e.empno,e.ename,d.dname,d.loc
    FROM t_dept d RIGHT JOIN t_employee e
    ON e.deptno=d.deptno;

3、合并两个查询

UNION和UNION ALL
区别：UNION得到的结果中不允许有行相同元素，UNION ALL得到的结果允许有行相同元素。就是集合定义上的区别。

SELECT *
    FROM t_astudent
UNION (ALL)
SELECT *
    FROM t_bstudent;

4、子查询

-- 查找与SMITH工资和职位一样的员工信息
SELECT ename,sal,job
    FROM t_employee
    WHERE (sal,job)=(
        SELECT sal,job
            FROM t_employee
            WHERE ename='SMITH');

-- 关键字: (NOT) IN

-- 关键字: ANY
>ANY(>=ANY):比最小的大就行了
<ANY(<=ANY):比最大的小就行了
例：
SELECT ename,sal
    FROM t_employee
    WHERE sal>ANY(
    SELECT sal
        FROM t_employee
        WHERE job='manager'
    );

-- 关键字: ALL
>ALL(>=ALL):比最大的还大
<ALL(<=ALL):比最小的还小
例：
SELECT ename,sal
    FROM t_employee
    WHERE sal>ALL(
    SELECT sal
        FROM t_employee
        WHERE job='manager'
    );

-- 关键字: EXISTS
例：有个部门表和员工表。如某部门没有员工就显示该部门
SELECT *
    FROM t_dept c
    WHERE NOT EXISTS(
        SELECT *
            FROM t_employee
            WHERE deptno=c.deptno
        );

-- 子查询典例
查询雇员表中各部门号、部门名称、雇员人数、平均工资
其中子查询得到的表另命名为employee。

SELECT d.deptno,d.dname,number,average
    FROM t_dept d INNER JOIN (
        SEALECT deptno dno,COUNT(empno) number,AVG(sal) average
            FROM t_employee
            GROUP BY deptno DESC) employee
    ON d.deptno=employee.dno;