第六单元：子查询

子查询需求场景：

语法：

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 = 'Able');

---

注意点：

• 单行比较必须对应单行子查询（返回单一结果值的查询）：比如 = ， >
• 对行比较必须对应多行子查询（返回一个数据结合的查询）：比如 in , > any , > all

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 than one row

正确写法：

select employee_id,last_name
from employees
where salary = (select min(salary)
                from employees);

多行举例：

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';

这段sql含义是查询出工作不是IT_PROG的，薪水小于工作是IT_PROG的员工最大薪水的员工相关信息。