SQL Server数据库培训——子查询
上一篇 / 下一篇 2011-03-31 14:35:59 / 个人分类：数据库
1. 子查询（subquery）
SQL支持在查询语句中编写查询或者嵌套其它查询。外层查询返回结果给调用者，内层查询（也称子查询）返回数据供外部查询使用。
通俗点说子查询是嵌套在SELECT、INSERT、UPDATE和DELETE语句的WHERE子句和HAVING子句中的另一个SELECT语句。
1.1 子查询类型
子查询可以分成独立子查询（self-contained subquery）和相关子查询(correlated subquery)。
独立子查询的特点是不依赖于外部查询，可以独立运行。而相关子查询需要依赖于它的外部查询。
独立子查询又分为标量子查询(scalar subquery)和多值子查询（multi-valued wubquery）和表子查询（table wubquery）。
子查询又分单列子查询和多列子查询，这在后面会讲到。
1.1.1 独立子查询
独立子查询是独立与外部查询的子查询，它总是可以拿出来单独运行。这种语句的执行分两个步骤：首先执行内部子查询，然后根据子查询的结果再执行外层查询。外部查询返回所有结果有调用者。子查询只运行一次。
1.1.1.1 标量子查询
标量子查询，由比较运算符（＝、>、<、<=、>=）引出的子查询。在使用单一比较操作符引出子查询时，必须保证子查询返回一个单值，否则将引起查询错误。
1.1.1.1.1 单行单列子查询
例子一：在where条件中使用子查询
SELECT *
FROM USER
WHERE user_id = (SELECT
ownerid
FROM dealers
WHERE ownerid > 0)
例子二：在delete条件中使用子查询
DELETE
FROM USER
WHERE user_id>=(SELECT
Avg(ownerid)
FROM dealers
WHERE ownerid > 0)
SELECT
d.DealerID,
d.DealerName,
(SELECT
u.login_name
FROM USER u
WHERE u.user_id = d.OwnerID) username
FROM dealers d
例四：在from子句中
SELECT
u.user_id,
d.DealerID,
d.ownerid
FROM USER u,
(SELECT
d.DealerID,
d.ownerid
FROM dealers d
WHERE d.DealerID = 1425) d
WHERE u.user_id = d.ownerid
上面所有例子中我们可能看到子查询
SELECT
ownerid
FROM dealers
WHERE ownerid > 0
是可以单独拿出来执行的。不会有语法错误。
1.1.1.1.2 单行多列子查询
当是单行多列的子查询时，外部查询语句的条件语句中引用子查询结果时也可用单行比较符号来进行比较（成对比较）。例如：
SELECT *
FROM USER u
WHERE (user_id,login_name) = (SELECT
u2.user_id,
u2.login_name
FROM USER u2
WHERE u2.user_id = 1)
单行多列子查询不能用在select子句中。
1.1.1.2 多值子查询
子查询返回多行数据叫多值子查询.关键字IN可以处理多值子查询。
例一：
SELECT *
FROM USER
WHERE user_idin(SELECT
ownerid
FROM dealers
WHERE ownerid > 0)
例二：
SELECT *
FROM USER
WHERE user_idNOT IN(SELECT
ownerid
FROM dealers
WHERE ownerid > 0)
SELECT *
FROM USER u
WHERE (user_id,login_name)IN(SELECT
u2.user_id,
u2.login_name
FROM USER u2
WHERE u2.auth_email = 1)
1.1.2 相关子查询
相关子查询是指引用了外部查询中出现的表的列的子查询。这就意味着它不能被单独地执行。
以下是相关子查询的举例
例一：
SELECT *
FROM relation_customer_product rcp
WHERE rcp.billing_id = (SELECT
MAX(rcp1.billing_id)
FROM relation_customer_product rcp1
WHERE rcp1.operator_id = rcp.operator_id)
上面的SQL返回的结果是所有注册人员注册的最后一个BBN用户信息。
在逻辑上相关子查询会为每个外部行单独计算一次,也就是说上表relation_customer_product中有多少行数据子查询就执行多少次。
当外部查询运行到第一行时，比如上表中RCP.operator_id= 571508时，子查询筛选出当前operator_id最大billing_id返回给外部查询，外部查询判断当前的billing_id是否等于子查询返回的billing_id，是则返回给用户，不是则不返回该行。依次类推。
相关删除举例
Delete
FROM relation_customer_product rcp
WHERE rcp.billing_id = (SELECT
MAX(rcp1.billing_id)
FROM relation_customer_product rcp1
WHERE rcp1.operator_id = rcp.operator_id)
同样的相子查询也分为单行单列、单行多列、多行单列、多行多列四种情况。不再详细描述。
1.1.2.1 EXISTs子查询
子查询支持exists谓词，它的输入是一个子查询。如果子查询能返回任何行，则exists返回true,否则返回false.
SELECT *
FROM dealers d
WHERE EXISTS(SELECT *
FROM USER u
WHERE d.OwnerID = u.user_id)
EXISTS存在一个所谓的’短路功能‘,也就是这个谓词只关系是否存在记录.用IN的话每一次扫猫user表都需要扫描完表的数据.而EXISTS当遇到存在的user_id会返回true或flase然后就不继续扫表下面的数据了
所以下面的这个SQL
SELECT *
FROM dealers d
WHERE ownerid IN (SELECT user_id
FROM USER u
WHERE d.OwnerID = u.user_id)
可以替换成
SELECT *
FROM dealers d
WHERE EXISTS(SELECT *
FROM USER u
WHERE d.OwnerID = u.user_id)
TAG:
标题搜索
日历
|
|||||||||
| 日 | 一 | 二 | 三 | 四 | 五 | 六 | |||
| 1 | 2 | 3 | 4 | 5 | 6 | ||||
| 7 | 8 | 9 | 10 | 11 | 12 | 13 | |||
| 14 | 15 | 16 | 17 | 18 | 19 | 20 | |||
| 21 | 22 | 23 | 24 | 25 | 26 | 27 | |||
| 28 | 29 | 30 | |||||||
我的存档
数据统计
- 访问量: 47088
- 日志数: 61
- 图片数: 3
- 建立时间: 2006-12-05
- 更新时间: 2011-10-31
