Microsoft SQL Server 2008技术内幕：T-SQL语言基础（子查询）

四、子查询

• 分类
  • 独立子查询
    • 返回值
      • 标量
      • 多个值
      • 整个表
  • 相关子查询
    • 返回值
      • 标量
      • 多个值
      • 整个表
• 作用
  • 避免将查询操作分为多个步骤，并在变量中保存中间查询结果

4.1 独立子查询

4.1.1 独立标量子查询

• 子查询只返回一个值

select orderid, orderdate, empid, custid
from sales.orders
where orderid = (select max(o.orderid)
				from sales.orders as o);

4.1.2 独立多值子查询

select orderid, orderdate, empid, custid
from sales.orders
where custid in (select c.custid
				from sales.customers as c
				where c.country = 'USA');

• 不需要在多值子查询中使用distinct，数据库引擎可以自动删除重复值

4.2 相关子查询

4.2.1 相关子查询

select orderid, orderdate, empid, custid
from sales.orders as o1
where orderid = (select max(o2.orderid)
				from sales.orders as o2
				where o1.custid = o2.custid);

4.2.2 exists

• ANSI
• 只返回true或false
• 与in可以起到相同效果
  • 区别
    • in为三值逻辑
      • 当子查询返回结果中存在null
      • not in 得到 unknown
      • 可能无法获得想要的结果
    • exists为二值逻辑

select custid, companyname
from sales.customers as c
where country = 'Spain'
and not exists(select * from sales.orders as o 
			where o.custid = c.custid)

select custid, companyname
from sales.customers as c
where country = 'Spain'
and custid not in (select custid from sales.orders)

4.3 高级子查询

4.3.1 返回前一个或后一个记录

select orderid, orderdate, empid, custid，
	(select max(o2.orderid)
	from sales.orders as o2
	where o2.orderid < o1.orderid) as prevorderid
from sales.orders as o1;

4.3.2 连续聚合

• 累计求和

select orderyear, qty,
	(select sum(o2.qty)
	from sales.ordertotalsbyyear as o2
	where o2.orderyear <= o1.orderyear) as runqty
from sales.ordertotalsbyyear as o1
order by orderyear;

4.3.3 行为不当的子查询

4.3.3.1 null的问题

• not in 处理返回值中存在null的子查询
• 解决方式
  • 方式一：子查询主动剔除null
  • 方式二：表结构将不应该为null的定义为not null
  • 方式三：使用exists

4.3.3.2 子查询列名中的替换错误

• 错误场景
  • 当子查询中引用列名时没有用表名前缀进行限定
  • 且列名在子查询表中不存在时
  • 会在外部表中继续查找该列，导致出现错误结果
• 处理方式
  • 使用表名前缀进行限定