【课堂笔记】【SQL】17.Retrieving Data Using Subqueries

5dm

IN的主查询，成对比较。

……WHERE (manager_id,department_id) in ((1,2),(3,4),……);

 

标量子查询：

示例：

SQL> select employee_id,last_name from employees e
  2  order by (select department_name from departments d
  3  where e.department_id=d.department_id);

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        205 Higgins
        206 Gietz
        200 Whalen
        100 King
        101 Kochhar
        102 De Haan
        112 Urman
        108 Greenberg
        109 Faviet
        110 Chen
        111 Sciarra

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        113 Popp
        203 Mavris
        107 Lorentz
        105 Austin
        103 Hunold

 

关联子查询：

语句的解析过程如图

登录/注册后可看大图

5dm

续：

EXISTS存在：功能有点类似IN。  NOT EXISTS就是不存在。

如果找到一条匹配的记录，则不再继续往子查询的查找。与IN比起来还是有很大优点的。

示例： 

SELECT employee_id, last_name, job_id, department_id

FROM   employees outer

WHERE  EXISTS ( SELECT 'X'

                 FROM   employees

                 WHERE  manager_id =

                        outer.employee_id);

 

关联更新：

示例：

 

UPDATE empl6 e

SET    department_name =

              (SELECT department_name

       FROM   departments d

       WHERE  e.department_id = d.department_id);

 

关联删除：

示例：

 

DELETE FROM empl6 E

WHERE employee_id = 

           (SELECT employee_id

            FROM   emp_history

            WHERE  employee_id = E.employee_id);

 

 

WITH，将同样的查询块定义起来将其结果放在临时表空间中，这样重复使用可以提供效率。注意，必须先定义再使用。

示例：

SQL> with
  2  dept_costs as
  3  (select d.department_name,sum(e.salary) as dept_total
  4  from employees e join departments d
  5  on e.department_id=d.department_id
  6  group by d.department_name),
  7  avg_cost as
  8  (select sum(dept_total)/count(*) as dept_avg
  9  from dept_costs)
 10  select * from dept_costs where dept_total>(select dept_avg
 11  from avg_cost)
 12  order by department_name;

DEPARTMENT_NAME                DEPT_TOTAL
------------------------------ ----------
Sales                              304500
Shipping                           156400

 

 

 

 

 

 

张清扬

另外一个说法 是exists操作符  只要子查询返回结果 不论这个结果是null 还是其他值 立刻返回。

同样 in  无论是否已经找到结果 会遍历子查询的结果集

实际工作中，exists和in都可以使用情况下，针对oltp，前者和后则的表现查询非常巨大。

子查询返回结果集越大，in就性能就越差。