SQL Server学习笔记5之--子查询与表表达式

1.子查询
这里不知道写什么重点,我觉得子查询分2种吧。一种是独立的子查询,和外部查询无关,它只为外部查询执行一次足矣.还有一种是相关的子查询,
它是外部查询没执行一行它就跑一次,是动态的.
我这里举个例子：
--学生表
create table #s(sno int,sname varchar(10))
--选课表
create table #sc(sno int,cno int,score int)
--课程表
create table #c(cno int,cname varchar(10))
--插入数据
insert #s select
1,'a' union all select
2,'b' union all select
3,'c'
insert #c select
1,'English' union all select
2,'Chinese' UNION ALL SELECT
3,'Computer'
insert #sc select
1,1,90 union all select
1,2,89 union all select
1,3,87 union all select
2,2,99 union all select
3,1,76 union all select
3,3,65
---查询出选修了全部课程的学生姓名-------
--------无关联子查询----------
select sname
from #s join #sc on #s.sno=#sc.sno
group by sname
having COUNT(cno)=(select COUNT(*) from #c)--这里就是无关联的子查询
-------相关子查询------------
select sname
from #s s
where not exists(select * from #c  c where not exists
(select *from #sc where sno=s.sno and cno=c.cno)
)
------结果-----------
/*
sname
----------
a
*/
上面通过学生选课的经典例子说明了用不同的子查询解决问题的一个用法。

说到EXISTS 不得不提到IN 这2个东西其实可以互相转换,但是在某些用法和效率上还是有差别的。
先来说说IN和EXISTS之间的一个区别：
IN不同于EXISTS，当输入列表包含NULL时候,它会产生一个UNKOWN。如IN (a,b,c,null)-->TRUE or UNKOWN.又因为在筛选器中UNKOWN的处理方式是false,
使用IN和使用EXIST产生的结果是一样的，优化器产生的执行计划是一样的。
但是 NOT EXISTS 和 IN之间的逻辑差别就大了,当然前提是输入列表中含有NULL的时候。
create table #test1(a int)
create table #test2(b int,c int)
insert #test1 values(1)
insert #test1 values(2)
insert #test1 values(3)
insert #test2 values(1,3)
insert #test2 values(null,2)
--NOT IN
select A from #test1 where a not in(select B from #test2)
/*
A
-----------
(0 行受影响)
*/
--NOT EXISTS
select A from #test1 where NOT EXISTS(SELECT * FROM #test2 WHERE #test1.a=b)
/*
A
-----------
2
3
*/
为什么在NOT IN里面没有返回2和3？ 这是因为 NOT IN (A,B,NULL)返回的结果永远是 NOT TRUE 或者 NOT UNKOWN(UNKOWN),不会返回TRUE。(关于三值逻辑的判断可以参见我以前写的博客)

我再举个例子：
a in (a,b,null) 返回TRUE。
a not in (a,b,null) 返回not true 即是false 。
c in(a,b,null) c本来不在列表里,但是因为里面有个NULL,SQL 不知道NULL是不是C，所以产生了一个UNKOWN,结果逻辑判断就FALSE.
c not in(a,b,null) 因为里面有个NULL,SQL 不知道NULL是不是C，所以产生了一个 not UNKOWN,即UNKOWN,结果逻辑判断就是FALSE.
这就是为什么NOT IN 和 NOT EXISTS的有时候的逻辑区别了。那么如何解决这个问题呢？你可以在子查询里加个判断,把NULL值去掉
select A from #test1 where a not in(select B from #test2 where b is not null)
这句的执行计划和NOT EXISTS是一样的。如果不加判断NULL 那么不但结果不对 而且效率也降低不少。因为它加了一个查找B是NULL的步骤，自己可以试试

对了,这里注意一个细节：
select A from #test1 where a not in(select B from #test2) 注意这里的子查询里的SELECT b 有些粗心的朋友会写成select a ,
select A from #test1 where a not in(select A from #test2) 这样不会报错,返回NULL,逻辑上还是可以以的,所以好的习惯可以在子查询的列名前都加上前缀,这样即使写错了也会报错的.
我们再来说说IN 和 EXISTS 效率上的一个区别
in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。
一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当，那么用in和exists差别不大。
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：
例如：表A（小表），表B（大表）
1：
select * from A where cc in (select cc from B)
效率低，用到了A表上cc列的索引；
select * from A where exists(select cc from B where cc=A.cc)
效率高，用到了B表上cc列的索引。
相反的
2：
select * from B where cc in (select cc from A)
效率高，用到了B表上cc列的索引；
select * from B where exists(select cc from A where cc=B.cc)
效率低，用到了A表上cc列的索引。
not in 和not exists
如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；
而not extsts 的子查询依然能用到表上的索引。
所以无论那个表大，用not exists都比not in要快。(HOHO,这个资料就贴下了，写得很好，我答题很喜欢用的O(∩_∩)O)
我这里想介绍个函数,我自己也是第一次看到.
问题描述；返回最小的缺失数,要求一定是正数.
IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1
(
  keycol  INT         NOT NULL PRIMARY KEY CHECK(keycol > 0),
  datacol VARCHAR(10) NOT NULL
);
INSERT INTO dbo.T1(keycol, datacol) VALUES(3, 'a');
INSERT INTO dbo.T1(keycol, datacol) VALUES(4, 'b');
INSERT INTO dbo.T1(keycol, datacol) VALUES(6, 'c');
INSERT INTO dbo.T1(keycol, datacol) VALUES(7, 'd');

--方法1：case when ....end
select
case when not exists(select * from T1 where keycol=1) then 1
else (select MIN(keycol) from T1 a  where not exists(select * from T1 where keycol=a.keycol+1))+1 end
--方法2;COALESCE(a,b)函数--具体翻MSDN吧
select coalesce(MIN(keycol+1),1)
from T1 a
where not exists(select * from T1 where keycol=a.keycol+1)
and exists(select * from T1 where keycol=1)--这个EXISTS如果为NULL where 条件为假，那么MIN(KEYCOL+1)得到一个NULL,那么函数取第二个参数1
--方法3：临时表(这里的临时表产生方法我在后面会说，当然我之前第一次的学习笔记也有，有兴趣的可以去翻翻)
select top 1 N
from T1 right join  NUM on T1.keycol=NUM.N--这里的NUN表是一个从1-1000000的表
where   N<=(select MAX(keycol) from T1 ) and keycol is null
--方法4：表之间的OUTER JOIN
SELECT case when not exists(select * from T1 where keycol=1) then 1
else (select MIN(A.keycol + 1)
FROM dbo.T1 AS A
  LEFT OUTER JOIN dbo.T1 AS B
    ON B.keycol = A.keycol + 1
WHERE B.keycol IS NULL)end
/*
N
-----------
1
*/
这里还有一些不常用的谓词,比如ANY ALL,some等,可以参考JJ大大写得文章http://blog.csdn.net/jinjazz/archive/2009/08/26/4487072.aspx

2.表表达式
表表达式很多，如视图,派生表,公用表表达式（CTE）等.我这简单介绍下派生表和CTE。
派生表：
首先注意一点：它是完全不存在的,只是为了代码的清晰和简化存在的,不会降低也不会提高性能.
关于他的列名:你必须为他指定唯一的列名,其实没什么好说的这个,我说个用法,实用性不大。
select *
from (select name,max(score),avg(socre)) as sScore(姓名,最高分,平均分)--就是在表外面指定列名
order by 姓名
它的多次引用是受限制的,你不能在一个查询里多次引用它,想这么做的时候需要重写,哪怕是COPY也要COPY下(CTE就没这个限制)
select * from (select no,score from k ) tb1 ,(select no,score from k ) tb2 where tb1.no=tn2.no+1 --我这里只是举个例子,没有意义的这个代码
CTE;
这个是SQL2005新增加的,个人比较喜欢。你可以将它理解为一个临时表.它虽然不支持嵌套,但是可以变相嵌套。
WITH t1 as
(select ...from tb),
t2 as
(select ... from t1),
t3 as
(select ....from t2)
select * from t3
下一个CTE可以用上面一个CTE，外部的查询可以访问所有的CTE(一个批处理内).

PS；在CTE的WITH之前的语句必须用;号结束.这是因为WITH在SQL里还有其他作用,避免歧义.
上面我提到了,派生表是不能多次引用了,但是CTE是不受限制的，它可以这么用。
WITH t1 as
(select ...from tb)
select a.* from t1 a join t1 b on .....
CTE也可以用于表的删除更新,因为对CTE的操作是会影响原来的表的。如：
IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1
(
  keycol  INT         NOT NULL PRIMARY KEY CHECK(keycol > 0),
  datacol VARCHAR(10) NOT NULL
);
INSERT INTO dbo.T1(keycol, datacol) VALUES(3, 'a');
INSERT INTO dbo.T1(keycol, datacol) VALUES(4, 'b');
INSERT INTO dbo.T1(keycol, datacol) VALUES(6, 'c');
INSERT INTO dbo.T1(keycol, datacol) VALUES(7, 'd');
go
with cte as
(
select *
from T1
where keycol>4
)
delete cte
select * from T1
/*
keycol      datacol
----------- ----------
3           a
4           b
*/
不得不提下个人觉得CTE最强大最实用的地方：递归中的应用.
--假设是单继承,我这意思就是一个孩子只有一个父亲
create table #testt(child int,pid int)
insert #testt select
1,null union all select
2,1 union all select
3,1 union all select
4,2 union all select
5,1 union all select
6,2 union all select
7,3 union all select
8,6 union all select
9,8
--查找所有指定孩子的父亲节点
declare @child int
set @child=8
;with cte as
(
select pid from #testt where [email=child=@child]child=@child[/email]
union all
select a.pid from #testt a join cte b on b.pid=a.child
)
select * from cte where pid is not null order by 1
/*
pid
-----------
1
2
6
*/
--查找指定节点所有的子节点
declare @pid int
set @pid=6
;with ctes as
(
select child from #testt where [email=pid=@pid]pid=@pid[/email]
union all
select a.child from #testt a join ctes b on a.pid=b.child
)
select * from ctes
/*
child
-----------
8
9
*/
我这只是举个最简单的BOM递归中CTE的运用（关于更多应用,以后会再介绍）

这里我介绍下这个递归里面的东西：
它包含2个东西：定位点成员-- select child from #testt where [email=pid=@pid]pid=@pid[/email] 就是第一条查询语句,
递归成员-- select a.child from #testt a join ctes b on a.pid=b.child 这条就是对CTE本身的引用的语句.\\\
可以这么想,每次对CTE本身的引用就是本次递归的"前一个结果集".CTE直到递归成员返回null的时候结束。
这里还有一个东西:每个CTE是有默认的递归层数的--100次.如果你想设置,甚至取消这个限制,可以这样
;with ctes as
(
select child from #testt where [email=pid=@pid]pid=@pid[/email]
union all
select a.child from #testt a join ctes b on a.pid=b.child
)
select * from ctes option (maxrecursion 1000)
这里的1000就是嵌套循环的次数上限;如果你想取消限制，设置其为0.