SQL学习记录

1、JOIN 的执行时机

select * from xm, org
select * from xm join org on xm.xmid=org.xmid

书中所说的顺序为两表会先进行笛卡尔连接，然后从连接后的虚拟表中应用过滤条件，去除不需要的行
查看执行计划发现，SQLServer并没有按照预想的方式进行执行，而是在连接的时候就进行过滤，
对于外连接，也不是在连接后再插入外部行，而是使用特殊的运算符执行外连接
重绕

select * from xm, org

执行计划中包含一个运算符SPool用于优化重绕，那么重绕是指什么呢?
重绕实际上是对于需要两个输入的运算符，第一个输入在对第二个输入进行运算的时候，
第二个输入回到第一个输入的开头重新对第一个输入扫描的操作，说的很乱，
给个例子第一个输入有两行（1， 2），第二个输入也是两行（a, b），a在和1做运算后，b还需要对1做一次运算，那就发生了一次重绕
下面例子去除了select * from xm, org中的重绕

select * into #temp from Org where XMID is not null;
CREATE UNIQUE CLUSTERED INDEX UNIQUE_ORG_ID ON #temp(ORGID)
CREATE UNIQUE INDEX UNIQUE_XM_ID ON #temp(XMID); 
select * from XM, #temp 

2、 ON 以及WHERE的区别

select * from xm, org where xm.xmid=org.xmid 
select * from xm join org on xm.xmid=org.xmid 

上述两句SQL是内连接的两种形式，事实上它们没有任何的区别，ON和WHERE的区别在于外连接是ON会添加外部行

select * from xm left join org on xm.xmid=org.xmid 

事实上使用where也可以实现左右连接，但是不推荐使用，如下

select xm.xmid from xm, org where xm.xmid*=org.xmid

一个让人迷惑的连接

select distinct top 100 OrgID
from XM
right 
join Org on XM.XMID=Org.XMID
where Org.XMID > 10000
group by OrgID
having count(OrgID) > 0
order by OrgID

这个很复杂的SQL语句最终执行计划里并不会使用XM表，因此在写SQL时看看执行计划，确定SQL按照自己的想法实现
3、 with cube|with rollup 添加汇总行

select OrgPID, type, max(_Levelcode) from org
group by orgpid, type
--with cube
select OrgPID, type, max(_Levelcode) from org
group by orgpid, type
--with rollup

4、 distinct VS group by
SQLServer 为这两个操作生成相同的执行计划

select OrgPID from org
group by orgpid
select distinct OrgPID as PID from org

5、 Order BY 聚集索引的顺序会影响Order by 的效率，非聚集索引的顺序可能会影响Order by的效率
select * from org order by orgid
6、 前置通配符会导致优化器放弃使用索引

select * from test
where ID like '0A72EA90%'
select * from test
where ID like '%0A72EA90%'

查询优化器总是为and使用索引，OR只有操作的字段都是索引列中的字段时才会使用索引，
NOT永远不会使用索引，优化器会尝试将NOT转换为AND或者OR操作

select * from test
where (ID like '0A72EA90%') and ([Index] = 0)
select * from test
where (ID like '0A72EA90%') or ([Index] = 0)
select * from test
where not (ID like '0A72EA90%')

7、不能被SQL语句的表象迷惑，下面两句话生成相同的执行计划

select XMID from XM 
where xmid = (select distinct XMID from ORG where org.XMID=XM.XMID)
select (select distinct XMID from ORG where org.XMID=XM.XMID) as XMID 
from XM 

8、问题：怎么才能实现先判断表中有没有某个列，有的话把这一列查出来

IF EXISTS (
SELECT 1 FROM SYSOBJECTS T1 INNER JOIN SYSCOLUMNS T2 ON T1.ID=T2.ID
WHERE T1.NAME='XM' AND T2.NAME='XMID'
)
select XMID from XM
IF EXISTS (
SELECT 1 FROM SYSOBJECTS T1 INNER JOIN SYSCOLUMNS T2 ON T1.ID=T2.ID
WHERE T1.NAME='XM' AND T2.NAME='ID'
)
select ID from XM

解决办法1：EXEC

IF EXISTS (
SELECT 1 FROM SYSOBJECTS T1 INNER JOIN SYSCOLUMNS T2 ON T1.ID=T2.ID
WHERE T1.NAME='XM' AND T2.NAME='ID'
)
EXEC('select ID from XM')

9、not in的风险 在in的列表中存在空值时会有风险

where XMID not in (
select XMID from #XM 
--where XMID is not null
)
select * from XM xm
where not exists(
select 1 from #XM xmtmp 
where xm.xmid = xmtmp.xmid
)

10、count
count(*)列出所有行的行数，count(XMID) 列出XMID非空行的行数，
count(0),为每一行增加一个常量列，列值为0，计算常量列行数，效果上等同于count(*)

select count(*), count(0), max(1), count(XMID) from Org