子查询

FROM 后面嵌套子查询

找出每个部门平均薪水的薪资等级

SELECT
    T.DEPTNO, T.AVGSAL, S.GRADE
FROM
    (SELECT
        DEPTNO, AVG(SAL) AS AVGSAL
    FROM
        EMP
    GROUP BY
        DEPTNO
    ) T
JOIN
    SALGRADE S
ON
    T.AVGSAL BETWEEN S.LOSAL AND S.HISAL;

WHERE 后面嵌套子查询

找出高于平均薪资的员工

SELECT
    ENAME, SAL
FROM
    EMP
WHERE
    SAL > (SELECT AVG(SAL) FROM EMP);

SELECT 后面嵌套子查询

找出员工所属的部门名称

SELECT
    E.ENAME,
    (
    SELECT
        D.DNAME
    FROM
        DEPT D
    WHERE
        E.DEPTNO = D.DEPTNO   
    )
FROM
    EMP E;

# 一般不这样用，了解即可