深入浅出SQL(12)-子查询

该系列文章系个人读书笔记及总结性内容，任何组织和个人不得转载进行商业活动！

 

子查询：查询中的查询 

 

有了联接确实很好用，我们可以将多张关联的表联接成一张临时表，然后设置条件，从中查询需要的记录；但有时要问数据库的数据不只一个，或者把甲查询的结果作为乙查询的输入，这时就需要子查询了；

 

子查询有助于避免数据重复，让查询更加动态灵活；

 

场景：

    Mark作为一名猎头顾问，他有一个管理联系人的数据库，并新建了许多表，来存储他的联系人；

    联系人：id-姓-名-性别-电话-邮箱-生日-专业-邮编（唯一标示地区）-兴趣；

    这样，Mark通过数据库管理职务与联系人之间的关系，方便在职位有空缺的时候，给合适的人推荐工作；

    

数据库模式图：

    基于联系人的表结构和我们之前分解的饮品数据库的结果类似；

    其中有联系人的表：my_contacts;

    我们在下图标明了表之间的数据模式；

 

 

现在Mark可以将它的联系人与他手里的工作进行配对：

    对于工作，Mark需要加入新的表；

    每个人都有一份工作或者是一份期待的工作，所以可以加入与my_contacts表一对一关系的表；

 

加入更多的表：

    添加一张表job_desired，记录某个联系人理想中的工作，与my_contacts表一对一；

    添加一张表job_current，记录某个联系人当前的工作职务，与my_contacts表一对一；

    由于并非联系人表中所有人都有工作内容，所以单独成为一张表，可以避免在my_contacts表中出现NULL；同时联系人的工作薪资等信息相对敏感，单独成表，方便限定特定人群访问；

    

上述这些还都只是Mark的信息表，他还需要创建一个包含职缺信息的简单表job_list；

    完善的模式图如下；

    新表job_current、job_desired都与联系人表时一对一关系，所以使用内连接可以轻松的将两张表联接成一张表；

    联系人表的主键列名我们修改为contact_id，这样使用自然联接（natural join）可以更加轻松；

 

 

我们新建数据库mark_list及其相应的表结构，并填入一些测试数据；

 

 

新建工作相关的3张表，并填入一些测试数据：

 

 

查询需求——使用内联接：

    我们想知道，特定空缺的职位都有哪些人更合适；

    如，我们要找一名EngineerS 薪资1600 至少4年工作经验的人；

 

1）使用自然联接：

mysql> SELECT mc.last_name,mc.first_name 
    -> FROM my_contacts mc NATURAL JOIN job_desired jd
    -> WHERE jd.title = 'EngineerS'
    -> AND jd.years_exp >= 4 
    -> AND 1600 BETWEEN jd.salary_low AND jd.salary_high;

 

2）使用内连接：

mysql> SELECT mc.last_name,mc.first_name 
    -> FROM my_contacts mc INNER JOIN job_desired jd
    -> ON mc.contact_id = jd.contact_id 
    -> AND jd.title = 'EngineerS' 
    -> AND jd.years_exp >= 4 
    -> AND 1600 BETWEEN jd.salary_low AND jd.salary_high;

 

直接对联接表设置条件，最终，我们都查出了结果：

+-----------+------------+
| last_name | first_name |
+-----------+------------+
| Mary      | DM         |
+-----------+------------+

 

查询方式——其他的查询：

    先从求职表中找到适合每项职缺的人，再利用my_contacts取得对应的联系方式，进而联系是否应征；

1）job_list中的职缺：

mysql> SELECT title 
    -> FROM job_list
    -> GROUP BY title
    -> ORDER BY title;
+-----------+
| title     |
+-----------+
| EngineerM |
| EngineerS |
+-----------+

 

2）使用关键字IN，检查 联系人中是否有意愿从事相应岗位的人：

mysql> SELECT mc.last_name,mc.first_name,mc.phone,jd.title
    -> FROM my_contacts mc NATURAL JOIN job_desired jd 
    -> WHERE jd.title IN ('EngineerM','EngineerS');
+-----------+------------+-------------+-----------+
| last_name | first_name | phone       | title     |
+-----------+------------+-------------+-----------+
| Joy       | HQ         | 15612121212 | EngineerM |
| Mary      | DM         | 13212121212 | EngineerS |
+-----------+------------+-------------+-----------+

 

我们注意到IN后的集合内容，就是我们第一个SQL查询的结果；

    我们使用了单独的一张表的查询结果，作为另外两张关联表的联接中间表的集合条件；

    ——那多个查询之间，能不能一起使用呢？

 

子查询：

    想用一个查询来完成两个查询的工作，需要在查询中添加子查询（subquery）；

    在上边这个例子中：

        第二个查询，从联接中间表中查询出了符合职缺的联系人的信息——外层查询（outer query）；

        里边的另外一个属于——内层查询（inner query）；

    子查询，是被另一个查询包围的查询，也可称为内层查询；

 

以子查询合二为一：

    把两个查询合并成一个；首先要有个外层查询，另一个查询中的查询就是内层查询；

    查询SQL如下，查询结果和分开查询时相同；

mysql> SELECT mc.last_name,mc.first_name,mc.phone,jd.title
    -> FROM my_contacts mc NATURAL JOIN job_desired jd 
    -> WHERE jd.title IN (
    -> SELECT title 
    -> FROM job_list
    -> GROUP BY title
    -> ORDER BY title
    -> );

 

子查询只不过是查询里的查询：

    外部的查询称为包含查询（containing query）或外层查询；

    内部的查询就是内层查询，或子查询；

    一般形式如下：

SELECT some_column

FROM table

WHERE column = （SELECT column FROM table）；

 

查询条件使用=号的话，子查询只能也只会返回单一值：

    这个值是特定行和列的交叉点，有时也称为cell，SQL称之为标量值（scalar value）；

    这个值会作为WHERE子句对比数据列内容的条件；

 

子查询示范：

    子查询就好像更加精确的WHERE子句；

mysql> SELECT last_name,first_name
    -> FROM my_contacts
    -> WHERE zip_code = (
    -> SELECT zip_code
    -> FROM zip_code
    -> WHERE city = '朝阳'
    -> );
+-----------+------------+
| last_name | first_name |
+-----------+------------+
| Joy       | HQ         |
+-----------+------------+
1 row in set (0.00 sec)

 

我们也可以使用联接实现这些查询：

mysql> SELECT mc.last_name,mc.first_name
    -> FROM my_contacts mc NATURAL JOIN zip_code zc
    -> WHERE zc.city = '朝阳';

 

子查询规则：

    子查询都是单一的SELECT语句；

    子查询总是位于圆括号中；

    子查询没有属于自己的分号；

    子查询能与INSERT DELETE UPDATE当然还有SELECT一起使用；

    子查询可以出现在四个地方：SELECT子句，选出列的列表作为其中一列，FROM子句，HAVING子句；

    多数情况，子查询只能返回单一值；结合IN时，可以寻找一组值的集合；

 

如何让查询结果中的名称短一些：

    为SELECT选择的列创建别名；

    注意，WHERE子句尚不能直接使用列别名；

 

 

我们看到，查询结果显示的更加简洁，并且这是临时表的别名，不会影响原表；

 

子查询的构造流程：

    “谁赚的钱最多？”

1）分解问题：

    ’谁’指last_name、first_name；

    ‘钱最多’指job_current表的salary列的MAX值；

 

2）找出能够回答部分问题的查询：

    MAX（salary）是个不错的选择；该函数返回指定列的最大值；

mysql> SELECT MAX(salary) 
    -> FROM job_current;

 

3）继续分解查询：

mysql> SELECT mc.last_name, mc.first_name
    -> FROM my_contacts mc;

 

4）最后，找出串起两个查询的方式：

    首先通过自然联接表job_current和my_contacts，找出每个人的薪资信息；

    然后加上WHERE子句以连接两段查询；

mysql> SELECT mc.last_name, mc.first_name,jc.salary
    -> FROM my_contacts mc NATURAL JOIN job_current jc;

mysql> SELECT mc.last_name, mc.first_name,jc.salary
    -> FROM my_contacts mc NATURAL JOIN job_current jc
    -> WHERE jc.salary = (
    -> SELECT MAX(jc.salary) 
    -> FROM job_current jc
    -> );

 

子查询并不是唯一的方式：

    使用自然联接和LIMIT也可以实现同样的目标；

mysql> SELECT mc.last_name, mc.first_name,jc.salary
    -> FROM my_contacts mc NATURAL JOIN job_current jc
    -> ORDER BY jc.salary DESC
    -> LIMIT 1;

 

查询结果：

+-----------+------------+--------+
| last_name | first_name | salary |
+-----------+------------+--------+
| Mary      | DM         |   2000 |
+-----------+------------+--------+

 

子查询的其他使用方式：

1）作为欲选取列的子查询：子查询能作为SELECT语句中选取的列之一；

mysql> SELECT mc.last_name, mc.first_name, 
    -> (SELECT city
    -> FROM zip_code
    -> WHERE mc.zip_code = zip_code) AS city
    -> FROM my_contacts mc;
+-----------+------------+--------+
| last_name | first_name | city   |
+-----------+------------+--------+
| Joy       | HQ         | 朝阳   |
| Mary      | DM         | 海淀   |
+-----------+------------+--------+

 

这段查询：

    查询出了my_contacts表的每一行，取出姓名城市名；

    而关于城市名的部分，利用了子查询比对my_contacts与zip_code表记录的zip_code，再从zip_code表中提取出城市名信息；

 

如果子查询放在SELECT语句中，用于表示某个欲选取的列，则一次只能从一列返回一个值；

 

示例：那些人的薪资比Joy高？

    Joy的薪资：

mysql> SELECT salary
    -> FROM my_contacts NATURAL JOIN job_current
    -> WHERE last_name = 'Joy';
+--------+
| salary |
+--------+
|   1000 |
+--------+

 

    将上述查询作为子查询，查询出哪些人比Joy的薪资高：

mysql> SELECT mc.last_name,mc.first_name,jc.salary
    -> FROM my_contacts mc NATURAL JOIN job_current jc
    -> WHERE jc.salary > (
    -> SELECT salary
    -> FROM my_contacts NATURAL JOIN job_current
    -> WHERE last_name = 'Joy'
    -> );
+-----------+------------+--------+
| last_name | first_name | salary |
+-----------+------------+--------+
| Mary      | DM         |   2000 |
+-----------+------------+--------+

 

非关联子查询：

    如果子查询可以独立运行且不会引用外层查询的任何结果，即称为非关联子查询；

    目前我们看到的都是非关联子查询（noncorrelated subquery）；

    软件先处理内层查询，查询结果再用于外层查询的WHERE子句，但是内层查询完全不需依赖外层查询的值，它本身就是一个可以完全独立运行的查询；

 

OUTER QUERY：外层查询较晚处理，它的查询结果取决于内层查询的值；

INNER QUERY：内层查询可以单独运行，连接会先处理这个部分；

 

现在我们只需知道从效率的角度来讲：

    交叉联接是件非常浪费时间的事，关联子查询也会拖慢速度；

    有时候最好创建测试数据库来尝试各种查询，比较查询运行的实践；

    联接比子查询更有效率；

 

实践：

    列出我的联系人中所有人的薪资与平均薪资之间的差距；

    使用子查询作为欲选择的列之一；

mysql> SELECT mc.last_name,mc.first_name,jc.salary,jc.salary - (
    -> SELECT AVG(salary)
    -> FROM job_current
    -> ) x_avg
    -> FROM my_contacts mc NATURAL JOIN job_current jc;
+-----------+------------+--------+-----------+
| last_name | first_name | salary | x_avg     |
+-----------+------------+--------+-----------+
| Joy       | HQ         |   1000 | -500.0000 |
| Mary      | DM         |   2000 |  500.0000 |
+-----------+------------+--------+-----------+

 

有多个值的非关联子查询：IN、NOT IN

    这里IN可以根据子查询的的整个结果集来评估条件列的每一行值；

    使用NOT IN可协助找出不符合结果集的值；

    这种查询也是非关联子查询，因为IN 和 NOT IN需要根据子查询的结果检查外层查询；

 

非关联子查询使用IN或NOT IN来检查子查询返回的值是否为集合的成员之一；

 

实践：

    列出每个邮政编码涵盖的地区中当前薪资最高的人；

mysql> SELECT mc.last_name, mc.first_name
    -> FROM my_contacts mc NATURAL JOIN job_current jc
    -> WHERE jc.salary IN (
    -> SELECT MAX(salary)
    -> FROM my_contacts mc NATURAL JOIN job_current jc
    -> GROUP BY mc.zip_code
    -> ORDER BY mc.zip_code DESC
    -> );
+-----------+------------+
| last_name | first_name |
+-----------+------------+
| Joy       | HQ         |
| Mary      | DM         |
+-----------+------------+

 

关联子查询：

    在非关联子查询中，内层查询（子查询）先被RDBMS解释，然后才输出到外层查询；

    关联子查询是指内层查询的解析需要依赖外层查询的结果；

 

实践：

    查询my_contacts表中有1项兴趣的人；

mysql> SELECT mc.last_name,mc.first_name
    -> FROM my_contacts mc 
    -> WHERE 1 = (
    -> SELECT COUNT(*)
    -> FROM contact_interest
    -> WHERE contact_id = mc.contact_id
    -> );
+-----------+------------+
| last_name | first_name |
+-----------+------------+
| Joy       | HQ         |
| Mary      | DM         |
+-----------+------------+

    

我们注意到：

    子查询也引用了关联名称mc-别名，他是在外层查询中创建的；

    外层查询必须先执行，执行完之后才能知道mc.contact_id的值，子查询依赖外层查询，他需要来自外层查询的结果；

 

一个搭配NOT EXISTS的关联子查询：

    关联子查询的常见用法是，找到所有外层查询结果里不存在于关联表里的记录；

    如：找到my_contacts表里没有工作的，即不在job_current表里的人的SQL可以这样写；

mysql> SELECT mc.last_name,mc.first_name
    -> FROM my_contacts mc
    -> WHERE NOT EXISTS
    -> (
    -> SELECT * FROM job_current jc
    -> WHERE mc.contact_id = jc.contact_id
    -> );

 

EXISTS与NOT EXISTS：

    和IN、NOT IN类似，子查询也能搭配EXISTS与NOT EXISTS；

    如：找到有兴趣的人；

mysql> SELECT mc.last_name,mc.first_name
    -> FROM my_contacts mc
    -> WHERE EXISTS
    -> (
    -> SELECT * FROM contact_interest ci
    -> WHERE mc.contact_id = ci.contact_id
    -> );
+-----------+------------+
| last_name | first_name |
+-----------+------------+
| Joy       | HQ         |
| Mary      | DM         |
+-----------+------------+

 

值得注意的是：

    子查询里可以嵌套子查询，虽然有层次的限制，但一般都够用；

    大多数情况下，如果不想使用子查询，只需要多学一些联接（下一章我们会介绍更多）；

 

总结：

1.外层查询：OUTER QUERY

    包含内层查询；

2.内层查询：INNER QUERY

    查询内的查询；

3.子查询：SUBQUERY

    被另一个查询包围的查询；

4.非关联子查询：NONCORRELATED SUBQUERY

    一个独立引用而且不引用outer query的任何部分的subquery；

5.关联子查询：CORRELATED SUBQUERY

    一个依赖outer query的返回结果的subquery；