高级子查询

2021.10.31
高级子查询

多列子查询

子查询返回的值含有多行
主查询与子查询返回的多个列进行比较

Main query
where (manager_id,department_id) in
subquery
100 90
102 60
124 60
--数字为返回值

在from子句中使用子查询

select last_name,e1.department_id,salary,e2.avg_sal
from employees e1,(select...avg(salary) avg_sal from ...) e2
where e1.department_id = e2.department_id

为子查询中的select语句写上别名，这样就可以在主查询的select语句和where中使用

包含case/decode

Oracle9中单列子查询可以在case/decode中使用

--显示员工的employee_id,last_name和location
--其中，若员工department_id与location_id为1800的department_id相同，则location为'Canada'，其余则为'USA'
select employee_id,last_name,
(case department_id when (select department_id from departments where location_id = 1800) then 'Canada'
                    else 'USA') location
from employees

相关子查询

按照一行接一行的顺序执行，主查询的每一行都执行一次子查询
即子查询中使用主查询相关的表，并向主查询中返回需要的值

exists/not exists操作符

检查在子查询中是否存在满足条件的行，存在则返回TRUE，反之则返回FALSE并继续查找

--查询公司管理者的下列信息
select employees_id,last_name,job_id,department_id
from employees e1
where exists(
            select 'A'
            from employees e2
            where e1.employee_id = e2.manager_id
            )--子查询中找到与e1中相等的id则返回该行
            --再在主查询中显示需要的该行信息

相关更新

可以利用相关子查询来更新另一个表中的信息

--找到t1与t2的id相同的行，并全部更新他们的column_1值
update table_1 t1
set column_1 = (
               select column_1
               from table_2 t2
               where t1.id = t2.id
               )

相关删除

delete from table t
where t_id = (subquery)

with子句

避免重复书写，将该子句中的语句块执行一次并存储到用户的临时表空间中，使用with可以提高查询效率

with
with_name as(select...)
with_name2 as(select...)
--将with_name和with_name2看成其中select语句的调用即可
select *
from with_name
where something > (select somethingelse from with_name2)
order by somename