什么是子查询
1.子查询是嵌套在其他查询中的常规的T-SQL查询(用括号括起来),当需要一个SELECT语句作为数据部分的基础或另一个查询中的条件的要素时,就是创建子查询。
2.子查询满足的需求:
(1).把一个查询分解成一系列的逻辑步骤。
(2).提供一个列表作为WHERE子句和 [ IN / EXISTS / ANY / ALL] 的目标对象。
(3).提供由父查询中每一条记录驱动的查找。
一.嵌套的子查询
1.嵌套子查询只朝一个方向进行——要么返回一个而用于外部查询的值,要么返回一个与IN运算符一起使用的的值的列表。
2.嵌套子查询中, 内部查询只处理一次。
3.与NULL比较总会得到NULL。如果允许NULL在子查询中出现,则在与NOT IN相比较时,外部查询中的所有行都会被确定为错误的——这将返回空的列表。
二.关联(相关)子查询
1.执行步骤:
(1) 外部查询得到一条记录并将其传入到内部查询。
(2)内部查询基于传入的值执行。
(3)内部查询从其结果中把值传回到外部查询,外部查询使用这些值来完成其处理。
注:
①在关联子查询中,对于外部查询返回的每一行数据,内部查询都要执行一次。
②在关联子查询中是信息流是双向的。
③外部查询的每行数据传递一个值给子查询,然后子查询为每一行数据执行一次并返回它的记录,外部查询根据返回的记录做出决策。
例:
SELECT title
FROM Movies Old
WHERE year < ANY
( SELECT year
FROM Movies
WHERE title = Old.title
);
SQL
中
EXISTS
的用法
比如在Northwind数据库中
有一个查询为
SELECT c. CustomerID, CompanyName
FROM Customers c
WHERE EXISTS (
SELECT OrderID
FROM Orders 0
WHERE 0.CustomerID = c.CustomerID )
;
这里面的EXISTS是如何运作呢?子查询返回的是OrderID字段,可是外面的查询要找的是CustomerID和CompanyName字段,这两个字段肯定不在OrderID里面啊,这是如何匹配的呢?EXISTS用于检查子查询是否至少会返回一行数据?
①该子查询实际上并不返回任何数据,而是返回值True或False
→他所带的子查询一般直接用 ‘SELECT *’ 因为给出列名也没多少意义。
②EXISTS指定一个子查询,检测行的存在。 (详见D ①)
③对于 EXISTS subquery 中的子查询subquery
是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)。
④结果类型Boolean 。如果子查询包含行,则返回 TRUE。
A、其实
:EXISTS属于相关子查询,也就是说子查询的条件依赖于外层父查询的查个属性值。比如:
SELECT Sname
FROM Student
WHERE EXISTS (
SELECT *
FROM C /*SC 为课程表, Student 为学生表 */
WHERE Sno=Student.Sno AND Cno ='1');
依赖也就是这一句“ Sno=Student.Sno”。 这个查询过程不是一般的自下而上执行,他与外查询依赖,执行的时候是先从父查询中取一个元组(该元组是全属性的),然后根据条件 Sno=Student.Sno 处理内查询,得到结果。(详见 D ①)然后进行父查询中取第二个元组,如此反复。
EXISTS 的子查询,有一些是不能被其它形式的子查询等价替换的,但是所有 IN、比较运算符、ANY 和ALL 的子查询都能用带有EXISTS 的子查询等价替换。但是在做的时候,请考虑效率问题,哪种高效用啊种,这是优化问题。
EXISTS只要知道内查询是否为空值就可以了,所以效率要比用IN的查询要高效一些。
SELECT Sname
FROM Student
WHERE EXISTS (
SELECT *
FROM C /*SC 为课程表, Student 为学生表 */
WHERE Sno=Student.Sno AND Cno ='1');
依赖也就是这一句“ Sno=Student.Sno”。 这个查询过程不是一般的自下而上执行,他与外查询依赖,执行的时候是先从父查询中取一个元组(该元组是全属性的),然后根据条件 Sno=Student.Sno 处理内查询,得到结果。(详见 D ①)然后进行父查询中取第二个元组,如此反复。
EXISTS 的子查询,有一些是不能被其它形式的子查询等价替换的,但是所有 IN、比较运算符、ANY 和ALL 的子查询都能用带有EXISTS 的子查询等价替换。但是在做的时候,请考虑效率问题,哪种高效用啊种,这是优化问题。
EXISTS只要知道内查询是否为空值就可以了,所以效率要比用IN的查询要高效一些。
B、在子查询中使用
NULL 仍然返回结果集:
这个例子在子查询中指定 NULL,并返回结果集,通过使用 EXISTS 仍取值为 TRUE。
SELECT CategoryName
FROM Categories
WHERE EXISTS (SELECT NULL)
;
C、比较使用
EXISTS 和 IN 的查询:
这个例子比较了两个语义类似的查询。第一个查询使用 EXISTS 而第二个查询使用 IN。注意两个查询返回相同的信息。
例
1
:
用
EXISTS :
SELECT
DISTINCT pub_name
FROM
publishers
WHERE EXISTS(
SELECT *
FROM titles
WHERE pub_id = publishers.pub_id
AND type = \'business\');
用
IN :
SELECT
DISTINCT pub_name
FROM
publishers
WHERE
pub_id IN (
SELECT pub_id
FROM titles
WHERE type = \'business\');
任一查询的结果集:
pub_name
----------------------------------------
Algodata Infosystems
New Moon Books
(2 row(s) affected)
例
2
:
本示例所示查询查找由位于以字母 B 开头的城市中的任一出版商出版的书名:
用
EXISTS :
SELECT
title
FROM
titles
WHERE EXISTS (
SELECT *
FROM publishers
WHERE pub_id = titles.pub_id
AND city LIKE \'B%\');
用
IN :
SELECT
title
FROM
titles
WHERE
pub_id IN (
SELECT pub_id
FROM publishers
WHERE city LIKE \'B%\');
任一查询的结果集:
title
------------------------------------------------------------------------
The Busy Executive\'s Database Guide
Cooking with Computers: Surreptitious Balance Sheets
You Can Combat Computer Stress!
Straight Talk About Computers
But Is It User Friendly?
Secrets of Silicon Valley
Net Etiquette
Is Anger the Enemy?
Life Without Fear
Prolonged Data Deprivation: Four Case Studies
Emotional Security: A New Algorithm
(11 row(s) affected)
D、进一步细究
EXISTS和IN:
若要判断某班是否存在一个名为"小明"的学生
用
IN :
"
小明" IN (
SELECT SNAME
FROM STUDENT);
其中
(select sname from student)
返回的是一个全班姓名的集合
,in用于判断"小明"是否为此集合中的一个数据;
用
EXISTS :
EXISTS
(SELECT *
FROM STUDENT
WHERE SNAME="
小明");
这两个函数是差不多的
, 但是由于优化方案的不同, 通常NOT EXISTS要比NOT IN 要快。因为NOT EXISTS可以使用结合算法而NOT IN 就不行了,而EXISTS则不如IN快
, 因为这时候IN可能更多的使用结合算法。
SELECT *
FROM
表A
WHERE EXISTS(
SELECT *
FROM
表B
WHERE
表B.id =
表A.id );
相当于
SELECT *
FROM
表A
WHERE id IN (
SELECT id
FROM
表B );
①对于表
A的每一条数据
,都执行
select * from
表B where
表B.id=
表A.id的存在性判断
,如果表
B中存在与表
A当前行相同的id ,即
exists为真
,则该行显示;否则不显示
②
exits适合内小外大的查询,in适合内大外小的查询
③
IN 确定给定的值是否与子查询或列表中的值相匹配。
④
EXISTS 指定一个子查询,检测行的存在。
本文转自 vfast_chenxy 51CTO博客,原文链接:http://blog.51cto.com/chenxy/727044,如需转载请自行联系原作者