子查询

子查询：sql支持在查询语句中编写查询，或者嵌套其他查询。最外层查询的结果集会返回给调用者，成为外部查询。
内部查询的结果是提供外部查询使用的，也称为子查询。内部查询可以取代基于常量或变量的表达式，并在运算时进行计算。与在表达式中使用常量不同的是，子查询的结果可能是变化的，因为被查询的表可能会发生变化。通过使用子查询，可以避免在解决方案中把操作分成多个步骤，并在变量中保存中间查询结果的需要。
子查询可以分成独立子查询（self-contained subquery）和相关子查询（correlated subquery）两类。独立子查询不依赖于它所属的外部查询，而相关子查询则须依赖于它所属的外部查询。
子查询的期望值可以是单值的、多值的或者以表为值。

独立子查询是独立于其外部查询的子查询。独立子查询调试起来非常方便，因为总可以把子查询代码独立出来单独运行，并确保实现默认的功能。在逻辑上，独立子查询在执行外部查询之前只要先执行一次，接着外部查询再使用子查询的结果继续进行查询。

独立标量子查询
标量子查询是返回单个值得子查询，而不管它是不是独立子查询。标量子查询可以出现在外部查询中期望使用单个值得任何地方（where， select等）

要查询数据库中orders表，返回这个表中订单id最大的订单信息。可以通过一个变量，用代码从orders表中取回最大的订单id，再把结果保存到这个变量中。接着代码就能够查询orders表筛选出订单id等于变量值的订单。

use TSQLFundamentals2008;
declare @maxid as int = (select max(orderid) from sales.orders);
select orderid, orderdate, empid, custid
from sales.orders where orderid = @maxid;

子查询只需要一条查询语句

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

对于有效的标量子查询，它的返回值不能超过一个。如果标量子查询返回了多个值，在运行时可能会失败。

独立多值子查询时一个在一个列中返回多个值得子查询，而不管子查询是不是独立的。一些谓词可以处理多值子查询。
IN谓词的格式可以是：<标量表达式>  IN (<多值子查询>)
如果标量表达式的值与子查询返回值中的任何一个值相等，IN谓词的计算结果就为true。

select orderid
from sales.orders
where empid in
    (select e.empid
    from hr.Employees as e
    where e.lastname like N'd%');

对于既能用子查询又能用联接查询的问题，先用直观的形式写出能解决问题的查询语句：如果对它运行的性能不满意，调整方法之一就是尝试重构查询。这样的查询重构可以包括用联接取代子查询，或者用子查询取代联接。为了最佳实践，在子查询返回的结果中应该排除null值。

要返回orders表中介于订单id的最小值和最大值之间不存在的订单id。

select n 
from TSQLFundamentals2008.dbo.nums
where n between (select min(o.orderid) from dbo.orders as o)
    and (select max(o.orderid) from dbo.orders as o)
    and n not in (select o.orderid from dbo.orders as o);

相关子查询是指引用了外部查询中出现的表的列的子查询。这就意味着子查询要依赖于外部查询，不能独立的调用它。在逻辑上，子查询会为每个外部行单独计算一次。

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

要查询sales.ordervalue视图，为每个订单返回当前订单金额占客户订单总额的百分比。可以写一个外部查询，对ordersvalue视图的一个名为o1的实例进行查询。在select列表中，把当前订单的金额除以相关子查询的结果。

select orderid, custid, val
    , CAST(100. * val / (select sum(o2.val)
                        from Sales.OrderValues as o2
                        where o2.custid = o1.custid)
            as numeric(5, 2)) as pct
from Sales.OrderValues as o1
order by custid, orderid;

exsit 谓词：支持一个名为exsit的谓词，它的输入是一个子查询：如果子查询能够返回任何行，该谓词则返回true，否则返回false。

select custid, companyname
from Sales.Customers as c
where country = N'spain'
    and exists
        (select * from Sales.Orders as o
        where o.custid = c.custid);

高级子查询：返回前一个或后一个记录（！没有顺序！）

-- 前一个（小于当前项最大的项）
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;
-- 后一个（大于当前项最小的项）
select orderid, orderdate, empid, custid
    , (select MIN(o2.orderid)
        from sales.Orders as o2
        where o2.orderid > o1.orderid) as nextorderid
from Sales.Orders as o1;

连续聚合（running aggregate）是一种对累计数据（通常是按时间顺序）执行的聚合

要返回每年的订单年份、订货量以及连续几年的总订货量，也就是每一年返回截止到该年的订货量的综合。

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;

行为不当（misbehaving）的子查询

null的问题

select custid, companyname
from sales.Customers
where custid not in (select o.custid
                    from sales.orders as o);

如果在orders表中插入一条custid为null的记录，则返回结果为空。先从行为符合期望效果的查询成分说起。对于下过订单的客户，in谓词返回true，因为子查询会返回这个客户。not运算符用于否定in为此，因此，not true的结果是false，外部查询不会返回这个客户。也就是说，当一个客户id在orders表中出现时，就可以确切地断定这个客户下过订单，因此不会在输出结果中出现。不过，如果orders表中有一个值为null的客户id，就不能确切的判断某个客户id不曾在orders表中出现过，为此做出简单的解释。对于没有在orders表的已知custid集中出现的客户（例如，客户22），in谓词返回unknown。in谓词之所以为这样的客户返回unknown，是因为它把这样的客户id与所有已知的客户id进行比较，得到的都是unknown。false or unknown，结果为unknown。举个更具体的例子，考虑表达式 22 not in（1，2，null）。可以把这个表达式改写为not 22 in（1，2，null）。继而把最后一个表达式扩展为（22 = 1 or 22 = 2 or 22 = null）。计算括号中每个单独表达式的真值，可以得到not（false or false or unknown），继而转化为unknown，其最终的计算结果为unknown。

简而言之，当对至少返回一个null值的子查询使用not in谓词时，外部查询总会返回一个空集。对于来自外部表的某个值，如果已经知道这个值会在子查询返回的集合中出现，那么外部查询将不会返回这个值，因为外部查询原本就是要返回不会在子查询中返回集合中出现的值；如果外部表中的这个值没有在子查询返回的已知值集合中出现，那么外部查询仍然不会返回这个值，因为这是无法确切的判断这个值是否包含null的集合所涵盖的范围。

遵循避免这个问题原则：
首先，当一个列不应该允许为null时，把它定义为not null很重要，加强数据的完整定义，比很多人想象的重要的多。其次，在你写的所有查询语句中，应该考虑三值逻辑可能出现的三种真值。明确地考虑一下查询是否要处理null值，如果要处理，对null值的默认处理是否适合需要。当不适合时，就要对查询语句进行调整。例如，在这个例子中，外部查询返回空集是因为与null值的比较。如果你想检查一个客户id是否在已知值得集合中出现，并忽略null值，就应该在查询中排除null值（可以显式或隐式地排除）。

-- 显式的排除null值得一个例子就是在子查询中增加谓词o.custid is not null
select custid, companyname
from Sales.Customers as c
where custid not in(select o.custid
                    from sales.Orders as o
                    where o.custid is not null
                    and c.custid = o.custid);

-- 隐式的排除null值得一个例子是使用not exists谓词取代not in谓词
select custid, companyname
from sales.Customers as c
where not exists
        (select o.custid
        from Sales.orders as o
        where o.custid = c.custid);
-- 与in不同的是，exists使用的二值逻辑。

子查询列名中的替换错误
代码中的逻辑缺陷有时是很难捉摸的。在这部分中，将介绍一个很难捉摸的代码缺陷，它和子查询列名中“无辜的”替换错误有关。

if OBJECT_ID('sales.myshippers', 'u') is not null
    drop table Sales.myshippers;

create table sales.myshippers
(
    shipper_id int not null
    , companyname nvarchar(40) not null
    , phone nvarchar(24) not null
    , constraint PK_MyShippers primary key(shipper_id)
);

insert into sales.myshippers(shipper_id, companyname, phone)
    values(1, N'Shipper GVSUA', N'(503) 555-0137');
insert into Sales.myshippers(shipper_id, companyname, phone)
    values(2, N'Shipper ETYNR', N'(425) 555-0136');
insert into sales.myshippers(shipper_id, companyname, phone)
    values(3, N'Shipper ZHISN', N'(415) 555-0138');

以下语句原本打算返回把订单发货给43号客户的发货人：

select shipper_id, companyname
from Sales.myshippers
where shipper_id in
    (select shipper_id
    from sales.orders
    where custid = 43);

sales.orders中没有shipper_id列造成错误
要避免这个问题，可以采用了两种最佳实践方法。从长远角度看，应该为在多个表之间使用统一的列名称而制定一宗策略且不应该低估它的重要性。从短期角度而言，可以采用一个非常简单的方法为子查询中的列名加上来源表的别名作为前缀。这样一来，列名解析处理就只能在制定的表中查找列名，如果没有找到，将生成一个名称解析错误。