SQL基础--子查询相关

SQL基础–子查询相关

---

通过子查询结果可把子查询分为以下几类：查值、查集合、查表

查值–子查询

查询员工部门名称

--查询结果作为返回字段
SELECT e.EMPLOYEE_ID,
       e.LAST_NAME,
       (SELECT d.DEPARTMENT_NAME
          FROM DEPARTMENTS d
         WHERE d.DEPARTMENT_ID = e.DEPARTMENT_ID) "DEPAT_NAME"
  FROM EMPLOYEES e;

查询部门名称是“Purchasing”的员工

--查询结果做为比较条件

SELECT e.EMPLOYEE_ID,
       e.LAST_NAME,
       e.DEPARTMENT_ID
FROM　EMPLOYEES e
WHERE e.DEPARTMENT_ID = (
      SELECT d.DEPARTMENT_ID
      FROM DEPARTMENTS d
      WHERE d.DEPARTMENT_NAME = 'Purchasing'
);

查集合–子查询

查询部门ID大于80的员工

--查集合子查询

SELECT e.EMPLOYEE_ID,
       e.LAST_NAME,
       e.DEPARTMENT_ID
FROM　EMPLOYEES e
WHERE e.DEPARTMENT_ID IN (
      SELECT d.DEPARTMENT_ID
      FROM DEPARTMENTS d
      WHERE d.DEPARTMENT_ID > 80
);

查表–子查询

查询部门工资最大工资

--查表子查询
SELECT d.DEPARTMENT_ID,d.DEPARTMENT_NAME,MAX_SAL
FROM DEPARTMENTS d,
     (SELECT DEPARTMENT_ID,MAX(SALARY) "MAX_SAL"
      FROM EMPLOYEES
      GROUP BY DEPARTMENT_ID) s
WHERE
s.DEPARTMENT_ID(+) = d.DEPARTMENT_ID;