Sql Server exists 子查询探究

Sql Server exists 子查询探究

EXISTS 代表存在量词 E,带有 EXISTS 谓词的子查询不返回任何数据,只产生逻辑真值 TRUE 或 逻辑假值 FALSE

例

工程表(prj)

字段名	数据类型	描述
prj_no	VARCHAR(40)	工程号
prj_name	VARCHAR(40)	工程名

---

物资出库表(out_stock)

字段名	数据类型	描述
mat_no	VARCHAR(40)	物资号
prj_no	VARCHAR(40)	工程名

---

物资表(stock)

字段名	数据类型	描述
mat_no	VARCHAR(40)	物资号
mat_name	VARCHAR(40)	物资名称

---

例:查询所有使用了 001 号物资的工程项目名称

代码:

SELECT prj_name
FROM prj
WHERE EXISTS (
    SELECT *
    FROM out_stock
    WHERE prj.prj_no = prj_no AND mat_no = '001'
)

分析:

本例中子查询的查询条件依赖于外层父查询的某个属性值(本例中是 prj 的 prj_no),因此也是相关子查询.这个查询语句的处理过程是首先取出外层查询(prj)表中的第一个元组,根据它与内层子查询相关的属性值(prj_no)处理内层子查询,若 WHERE 子句的返回值为 TRUE,则取此元祖放入结果表;然后再取(prj)表的下一个元组,重复这个过程,直到(prj)表全部检查为止.

---

相关子查询

相关子查询的执行依赖于外部查询。多数情况下是子查询的WHERE子句中引用了外部查询的表。
执行过程：

1. 从外层查询中取出一个元组，将元组相关列的值传给内层查询。
2. 执行内层查询，得到子查询操作的值。
3. 外查询根据子查询返回的结果或结果集得到满足条件的行。
4. 然后外层查询取出下一个元组重复做步骤1-3，直到外层的元组全部处理完毕。

不相关子查询

不相关子查询:子查询的查询条件不依赖于父查询的称为不相关子查询

---

由 EXISTS 引出的子查询,其子查询目标属性列一般用 * 表示,因为 EXISTS 子查询只关注结果集中是否有数据,有数据返回 TRUE,否则返回 FALSE

一些带 EXISTS 或 NOT EXISTS 谓词的子查询不能被其他形式的子查询等价替换.但所有带 IN 谓词,比较运算符,ANY 和 ALL 谓词的子查询都能用 EXISTS 谓词的子查询等价替换.

查询被所有的抢修工程项目都使用了的物资名称及规格

-- 题目的意思等价于 : 查询这样的物资,没有一个抢修工程没有使用过它

-- 外部查询
SELECT mat_name
FROM stock
WHERE NOT EXISTS (
    -- 中部查询
    SELECT *
    FROM prj
    WHERE NOT EXISTS (
        -- 内部查询
        SELECT *
        FROM out_stock
        WHERE mat_no = stock.mat_no
        AND prj_no = prj.prj_no
    )
)

过程分析:

选出一行物资(stock)信息 m1,选出一行工程(prj)信息 p1,如果在出库表(out_stock)中找不到 m1 + p1,说明 p1 没有使用 m1,此时中部查询的结果集不为空,外层查询 NOT EXISTS 值为 false,则 m1 被排除,当内层查询的返回值结果集为空时,说明物资 m1, 至少有一个工程没有使用它.内层的 NOT EXISTS()为 false 时,说明 out_stock 表中,存在 m1 和 p1,说明 物资 m1 被 p1 使用了,此时中部查询的结果集不会有 p1,当中部查询遍历完成后的结果集仍为空时,外层的 NOT EXISTS()值为 true,则表示物资 m1 被所有的工程都使用了,最后返回的结果是被所有工程都使用了的物资.

查询所用物资包含抢修工程’A1’所用物资的抢修工程号

-- 不存在这样的物资y,抢修工程'A1'使用了物资y,而抢修工程x没有使用物资y

SELECT prj_no
FROM prj
WHERE NOT EXISTS (
    SELECT *
    FROM out_stock s2
    WHERE s2.prj_no = 'A1' AND NOT EXISTS (
        SELECT *
        FROM out_stock s3
        WHERE s2.mat_no = s3.mat_no
        AND prj.prj_no = s3.prj_no
    )
)

过程分析:

选出一行工程(p1)信息,选出一行物资(m1,p2)信息,首先判断 p2 的工程号是否是 A1,如果是的,继续判断内部 NOT EXISTS, 如果内部返回的结果集不为空,此时 NOT EXISTS 是 false,如果每一个被 A1 使用的物资在内层判断的结果集不为空,那么它本身工程号,肯定满足题意,如果 p2 不为 A1,

查询使用了物资 ‘m1’ 和物资 ‘m2’ 的工程号,使用 EXISTS 子句

-- 不存在这样的工程,不使用 m1 或 不使用 m2

-- 外部查询
SELECT prj_no
FROM prj
WHERE NOT EXISTS (
    -- 不存在数据，外层循环返回 true
    -- 中部查询
    SELECT *
    FROM stock
    WHERE mat_no IN ('m1', 'm2') AND NOT EXISTS (
        -- 内部查询
        SELECT *
        FROM out_stock
        WHERE mat_no = stock.mat_no
        AND prj_no = prj.prj_no
    )
)

过程分析:

首先取出外部查询的一行元祖(p1),然后逐行遍历中部查询的(m1),然后判断中部查询的条件是否成立,总的条件分为四种:

1. mat_no IN (‘m1’, ‘m2’) 返回 true, 内部查询结果集中不为空,NOT EXISTS 返回 false
2. mat_no IN (‘m1’, ‘m2’) 返回 true, 内部查询结果集中为空,NOT EXISTS 返回 true
3. mat_no IN (‘m1’, ‘m2’) 返回 false, 内部查询结果集中不为空,NOT EXISTS 返回 false
4. mat_no IN (‘m1’, ‘m2’) 返回 false, 内部查询结果集中为空,NOT EXISTS 返回 true

分析四种情况:

1. 当 mat_no IN (‘m1’, ‘m2’) 返回 true 时,如果内部查询结果集不为空,说明找到了这个数据满足题意,此时整个中部查询的 WHERE 返回 false,中部查询结果集中不会添加数据集
2. 当 mat_no IN (‘m1’, ‘m2’) 返回 true 时,如果内部查询结果集为空,说明当前遍历的数据不满足题意,此时整个中部查询的 WHERE 返回 true,中部查询结果集中会添加数据集,此时中部查询的数据集中不再为空,本应该满足 mat_no IN (‘m1’, ‘m2’) 时,也应该满足内部查询条件,结果违反题意,外部查询结果直接返回 false,终止遍历
3. (3 和 4 属于同一种情况)当 mat_no IN (‘m1’, ‘m2’) 返回 false 时,无论内部查询结果集是否为空,此时整个中部查询的 WHERE 返回 false,不会影响整个中部查询的结果集

中部查询的结果集分为两种:

1. 结果集不为空:表明当前遍历的(p1)不满足题目条件
2. 结果集为空:表明当前遍历的(p1)满足题目条件