chapter09_子查询

• (NOT) IN 可以表示在集合中取值

  示例

    SELECT mc.name, jc.title FROM job_current AS jc NATURAL JOIN my_contacts AS mc WHERE jc.title IN ('Cook', 'Waiter', 'Writer', 'Web Developers');
  

• 可以用__子查询__的方式代替上个示例中 (NOT) IN 的部分

  示例

    SELECT mc.name, jc.title FROM job_current AS jc NATURAL JOIN my_contacts AS mc WHERE jc.title IN (SELECT title FROM job_listings);
  

• 使用子查询的结果既可以用 IN, 也可以 =;

  使用 IN 代表从取值在集合中，=代表取值为单个值;

  当使用 = 时，要确保子查询的结果为单一值，否则会报错

• 子查询都可以与增删改查INSERT, DELETE, UPDATE, SELECT配合使用

• 子查询的构造流程

  (1) 分解问题

  (2) 找出能够回答部分问题的查询

  (3) 找出串起两个查询的方式

• 子查询可以用在四个地方

  (1) SELECT 子句

  (2) FROM 子句

  (3) HAVING 子句

  (4) 选出 COLUMNlIST作为其中一列

• 子查询可以选取列

  示例

    SELECT mc.first_name, mc.last_name, (SELECT state FROM zip_code WHERE mc.zip_code = zip_code) AS state FROM my_contacts AS mc;
  

  这段查询表示， 从my_contacts表中获得姓，名，州的信息，其中州的信息是利用子查询对比 my_contacts与zip_code表记录的邮政编码,再从zip_code表中取出州名信息

• 非关联子查询： 子查询是一个独立的查询，不依赖外层查询

  关联子查询：子查询依赖外层查询

  示例

    SELECT mc.name, mc.email FROM my_contacts AS mc WHERE NOT EXISTS (SELECT * FROM job_current AS jc WHERE mc.contact_id = jc.contact_id);
  

  内层的子查询依赖了外层的mc.contact_id

• EXIST 和 IN 的区别

  (1) EXISTS：将外查询结果的每一行，代入到内查询中进行验证（保留内查询中返回True的记录，所以内查询用SELECT * 也没问题）

  (2) IN：将内查询结果的每一行，代入到外查询中进行验证

  (3) EXISTS示例：

    SELECT name FROM student AS s1 
    WHERE EXISTS (SELECT * FROM student AS s2 WHERE s1.dept = s2.dept AND s2.name = 'Lily');
  

  (4) 事实上EXIST常用的是否定形式 NOT EXIST

• 子查询的结果可以参与运算

  示例：获取每个Web Developers的姓名和相比于平均薪水超出的部分

    SELECT mc.first_name, mc.last_name, jc.salary - (SELECT AVG(salary) FROM job_current WHERE title = 'WEB Developers'), FROM my_contacts AS mc NATURAL JOIN job_current AS jc WHERE jc.title = 'WEB Developers';
  

  示例：获取当前薪水最高的人对应的邮政编码

    SELECT first_name, last_name FROM my_contacts WHERE zip_code IN (SELECT mc.zip_code IN (SELECT mc.zip_code FROM my_contacts AS mc NATURAL JOIN job_current AS jc WHERE jc.salary = (SELECT MAX(salary) FROM job_current));
  

• 联接比子查询更有效率

• 任何子查询能实现的事情都可以用联接实现