【OracleDB】 08 子查询

 什么是子查询？

子查询是一种常用计算机语言SELECT-SQL语言中嵌套查询下层的程序模块。

当一个查询是另一个查询的条件时，称之为子查询。

 

Oracle的子查询语法公式：

SELECT    select_list
FROM    table
WHERE    expr operator
             (SELECT    select_list
                FROM        table);

特点：

　　-　子查询 (内查询) 在主查询之前一次执行完成。

　　-　子查询的结果被主查询(外查询)使用 。

案例：

SELECT last_name
FROM   employees
WHERE  salary >
               (SELECT salary
                FROM   employees
                WHERE  last_name = 'Abel');

 

子查询的注意事项：

-　子查询要包含在括号内。

-　将子查询放在比较条件的右侧。

-　单行操作符对应单行子查询，多行操作符对应多行子查询。

 

---

子查询的返回类型：

-　单行子查询

-　多行子查询

 

单行子查询的特点：

-　只返回一行。

-　使用单行比较操作符。

 

单行比较操作符：

-　Equal To　=

-　Greather Than　>

-　Greather Than or Equal To　>=

-　Less Than　<

-　Less Than or Equal To　<=

-　Not Equal To　<>

 

案例：

返回job_id与141号员工相同，salary比143号员工多的员工和姓名，job_id 和工资

使用单行子查询

SELECT last_name, job_id, salary
FROM   employees
WHERE  job_id =  
                (SELECT job_id
                 FROM   employees
                 WHERE  employee_id = 141)
AND    salary >
                (SELECT salary
                 FROM   employees
                 WHERE  employee_id = 143);

 

---

子查询中使用分组函数

案例：

返回公司工资最少的员工的last_name,job_id和salary

子查询 + 分组函数

SELECT last_name, job_id, salary
FROM   employees
WHERE  salary = 
                (SELECT MIN(salary)
                 FROM   employees);

 

---

子查询中的 HAVING 子句

实现步骤：

-　首先执行子查询。

-　向主查询中的HAVING 子句返回结果。

 

案例：

查询最低工资大于50号部门最低工资的部门id和其最低工资

实现：

SELECT   department_id, MIN(salary)
FROM     employees
GROUP BY department_id
HAVING   MIN(salary) >
                       (SELECT MIN(salary)
                        FROM   employees
                        WHERE  department_id = 50);

 

---

非法使用子查询

SELECT employee_id, last_name
FROM   employees
WHERE  salary =
                (SELECT   MIN(salary)
                 FROM     employees
                 GROUP BY department_id);

错误警告：

多行子查询使用单行比较符

ERROR at line 4:
ORA-01427: single-row subquery returns more thanone row

 

---

子查询中的空值问题

SELECT last_name, job_id
FROM   employees
WHERE  job_id =
                (SELECT job_id
                 FROM   employees
                 WHERE  last_name = 'Haas');

返回结果：无任何记录行

no rows selected

 

---

多行子查询

特点：

-　返回多行。

-　使用多行比较操作符。

多行比较操作符

-　匹配IN列表中的任意一个　IN

-　匹配子查询返回的某一个　ANY

-　匹配子查询返回的所有记录　ALL

【ANY & ALL有什么区别？】

 

多行子查询中使用 ANY 操作符

题目：

返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary

实现：

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary < ANY
                    (SELECT salary
                     FROM   employees
                     WHERE  job_id = 'IT_PROG')
AND    job_id <> 'IT_PROG';

 

多行子查询中使用 ALL 操作符

题目：

返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary

实现：

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary < ALL
                    (SELECT salary
                     FROM   employees
                     WHERE  job_id = 'IT_PROG')
AND    job_id <> 'IT_PROG';

 

多行子查询的空值问题：

SELECT emp.last_name
FROM   employees emp
WHERE  emp.employee_id NOT IN
                             (SELECT mgr.manager_id
                              FROM   employees mgr);

no rows selected