第三章 表联接

交叉联接只有一个步骤-- 笛卡儿积（Cartesian Product）
内联接有两个步骤-- 笛卡儿积、过滤
外联结有三个步骤-- 笛卡儿积、过滤（filter）、添加外部行

交叉联接

use TSQLFundamentals2008;

select
    c.custid
    , e.empid
from
    Sales.Customers as c
    cross join hr.Employees as e;

使用ansi sql-92语法，要在参与联接的两个表之间使用"CROSS JOIN"关键字 。
当列名没有歧义是可以不添加表明前缀，但是为了明确起见，使用表名前缀是个良好的习惯

-- ansi sql-89语法
select
    c.custid
    , e.empid
from
    sales.Customers as c
    , hr.Employees as e;

自交叉联接

对同一个表的多个实例也可以进行连接，这种功能就是所谓的自联接（self-join），所有基本联接类型都支持自联接。

select
    e1.empid, e1.firstname, e1.lastname
    , e2.empid, e2.firstname, e2.lastname
from
    hr.Employees as e1
    cross join hr.Employees as e2;

生成数字表
由一列整数组成的结果集使用交叉联接处理起来非常方便。

use tempdb;
if object_id('dbo.digits', 'u') is not null drop table dbo.digits;
create table dbo.digits(digit int not null primary key);

insert into dbo.digits(digit)
values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

-- 写一个查询，生成从1到1000的整数序列。
select d3.digit * 100 + d2.digit * 10 + d1.digit + 1 as n
from dbo.digits as d1
    cross join dbo.digits as d2
    cross join dbo.digits as d3
order by n;

内联接要应用两个逻辑查询处理步骤：它首先像交叉联接一样，对两个输入表进行笛卡儿积运算；然后根据用户指定的谓词对结果进行过滤。

使用ansi sql-92语法须在两个表名之间指定inner join关键字。inner关键字是可选的，因为内联接是默认的联接方式，所以可以单独指定join关键字。用于对行进行过滤的谓词是在一个称为on子句的特别设计的语句中指定的，该谓词也称为联接条件。

use TSQLFundamentals2008;

select
    e.empid
    , e.firstname
    , e.lastname
    , o.orderid
from
    hr.Employees as e
    join Sales.orders as o
        on e.empid = o.empid;

-- ansi sql-89 语法
-- 和交叉联接类似，内联接也可以用ansi sql-89语法来表达。可以像交叉联接那样在表名之间放一个逗号。
-- 没有on子句
select
    e.empid
    , e.firstname
    , e.lastname
    , o.orderid
from
    hr.Employees as e
    , Sales.Orders as o
where
    e.empid = o.empid;

强烈推荐坚持使用ansi sql-92的联接语法，因为在某些方面它用起来更安全。假如想写一条内联接查询，但不小心忘记指定联接条件。如果这时用的是ansi sql-92语法，查询语句将是无效的，语法分析器会报错。
例如

select e.empid, e.firstname, e.lastname, o.orderid
from hr.Employees as e join Sales.Orders;

特殊的联接实例，包括组合联接（composite join）、不等联接（non-equi join）、以及多表联接（multi-table join）

组合联接就是联接条件涉及联接两边的多个列的查询。当需要根据主键-外键关系来联接两个表，而且主外键关系是组合的（即，关系基于多个列）时，通常就要使用组合联接。
假设现在要对tsqlfundamentals2008数据库的orderdetails表中列值得更新进行审核。

-- 为此新建一个名为orderDetailsAudit的客户审核表
use TSQLFundamentals2008;
if object_id('Sales.OrderDetailsAudit', 'u') is not null
    drop table Sales.OrderDetailsAudit;
create table sales.OrderDetailsAudit
(
    lsn int not null identity
    , orderid int not null
    , productid int not null
    , dt datetime not null
    , loginname sysname not null
    , columnname sysname not null
    , oldval sql_variant
    , newval sql_variant
    , constraint PK_OrderDetailsAudit primary key(lsn)
    , constraint FK_OrderDetailsAudit foreign key(orderid, productid)
        references sales.OrderDetails(orderid, productid)
);

-- 现在要写一个查询，返回在OrderDetails表的qty列上发生的所有取值变化，
-- 而且在每个结果行中还要返回该列在OrderDetails表中的当前值，以及OrderDetailsAudit
-- 表中变化前后的值。这时就要基于主键-外键关系对两个表进行联接如下。
select
    od.orderid
    , od.productid
    , od.qty
    , oda.dt
    , oda.loginname
    , oda.oldval
    , oda.newval
from
    Sales.orderdetails as od
        join Sales.orderdetailsaudit as oda
            on od.orderid = oda.orderid
            and od.productid = oda.productid
where
    oda.columnname = N'qty';

如果联接条件只包含等号运算符，那么这样的联接叫做等值联接（equi join）。如果联接条件包含除等号以外的其他运算符，那么这样的联接叫做不等联接（non-equi join）。

select
    e1.empid, e1.firstname, e1.lastname
    , e2.empid, e2.firstname, e2.lastname
from
    hr.Employees as e1
        join hr.Employees as e2
            on e1.empid < e2.empid;

一个联接表运算符只对两个表进行操作，而一条查询语句可以包含多个联接。通常，当from子句中包含多个表运算符时表运算符在逻辑上是按从左到右的顺序处理的。也就是说，第一个表运算符的结果表将作为第二个表运算符的输入，第二个表的运算符的结果将作为第三个表运算符左边的输入，以此类推。所以如果from子句中包含多个联接，逻辑上只有第一个联接对两个基础表进行操作，而其他联接则将前一个联接的结果作为其左边的输入。当处理交叉联接和内联接时，为了优化的目的，数据库引擎能够对联接顺序进行内部的调整，因为这样的优化并不会影响查询结果的正确性。

select
    c.custid, c.companyname
    , o.orderid, od.productid, od.qty
from
    Sales.customers as c
    join Sales.orders as o
        on c.custid = o.custid
    join Sales.OrderDetails as od
        on o.orderid = od.orderid;

与其他类型的联接相比，外联接的掌握通常要更难些。外联接基础是在ansi sql-92中才被引入的，因此它只有一种标准语法--在表名之间指定join关键字，在on子句中指定连接条件。外联接会应用内联接所应用的两个逻辑处理步骤，此外还多加一个特有的第三步：添加外部行。
在外联接中，要把一个表标记为“保留的”表，可以在表名之间使用关键字LEFT OUTER JOIN、RIGHT OUTER JOIN 以及FULL OUTER JOIN，其中OUTER关键字是可选的。
LEFT关键字表示左边表的行是保留的，RIGHT关键字表示右边表的行是保留的，而FULL关键字则表示左右两边表的行都是保留的。
外联接的第三个逻辑查询处理步骤就是要识别保留表中按照ON条件在另一个表找不到与之匹配的那些行，再把这些行添加到联接的前两个步骤生成的结果表中；对于来自联接的非保留表的那些列，追加的外部行中的这些列则用null作为占位符。

select
    c.custid
    , c.companyname
    , o.orderid
from
    Sales.Customers as c
    left outer join sales.Orders as o
        on c.custid = o.custid;

customers表中有两个客户没有任何订单，他们的id分别是22和57。查询结果中这两个客户在orders表中的列值都为null。从逻辑上来说，这两个客户相关的数据行在联接的第二步（基于ON谓词条件的过滤）就被过滤掉了，而在第三步又把这些行作为外部行添加了进来。如果使用内联接，结果将不会返回这两行。添加这两行后，就可以在结果中保留左边表的所有行。从外联接保留表的角度看，可以认为外联表结果中的数据行包括两种：内部行和外部行。内部行是指按照ON子句中的条件能在联接的另一边找到匹配的那些行；而外部行则是指找不到匹配的那些行。内联接只返回内部行，而外联接同时返回内部行和外部行。

使用外联接时，经常会为到底是在查询的on子句中，还是在where子句中指定联接条件而感到困惑。从外联接保留表中的行来考虑这个问题，ON子句中的过滤条件不是最终的。换句话中，ON子句中的条件并不能最终决定保留表中部分行是否会在结果中出现，而只是判断是否能够匹配另一边表中的某些行。所以，当需要表达一个非最终的条件时（即这个条件只决定哪些行可以匹配非保留表），就在ON子句中指定连接条件。当在生成外部行以后，要应用过滤器，而且希望过滤条件是最终的，就应该在where子句中指定条件。WHERE子句是在FROM子句之后被处理的，即在处理完所有表运算符，（在外联接条件下）生成了所有外部行以后。此外，与ON子句不同，对于行的过滤来说，WHERE子句是最终的。

下列语句返回没有订单的用户，即返回外部行。

select 
    c.custid
    , c.companyname
from
    Sales.Customers as c
    left outer join sales.Orders as o
        on c.custid = o.custid
where o.orderid is null;

选择联接的非预留表中的哪个列作为过滤器很重要。应该选择只在外部行才取值为null，而在其他行取值不为null的某个列。为此，有三种情形可以考虑安全的使用--主键列、联接列，以及定义为not null的列。主键列的取值不能为null；因此如果这样的列上出现了null，就意味着该行为外部行。如果某行的联接列的值为null空，则该行在联接的第二步就会被过滤掉，所以如果某行的联接列的值为null，则说明该行为外部行。同理，如果在定义为NOT NULL的列上出现了NULL值，则该行明显也为外部行。

外联接的高级主题：
包含缺少值的数据：在查询数据时，可以用外联接来识别和包含缺少的值（即null值）。例如，假设现在需要查询tsqlfundamentals2008数据库中orders表的所有订单，要确保对于2006年1月1日到2008年12月31日之间的每个日期至少在输出结果中出现一行。对于在这个时间范围内有订单的日期，不需要做其他特殊处理；但对于输出没有订单的日期，则其订单列用null作为占位符。
为了解决这个问题，可以先写一条查询语句返回要求的日期范围内的所有日期序列。再对这个日期集和orders表执行一个左联接操作。

-- 新建并填充辅助表Nums的代码
set nocount on;
use TSQLFundamentals2008;
if object_id('dbo.Nums', 'u') is not null drop table dbo.nums;
create table dbo.nums(n int not null primary key);

declare @i as int = 1;
begin tran
    while @i <= 100000
    begin
        insert into dbo.nums(n) values(@i);
        set @i = @i + 1;
    end
commit tran
set nocount off;

-- 生成指定范围内所有日期序列。
select DATEADD(day, n - 1, '20060101') as orderdate
from dbo.nums
where n <= DATEDIFF(day, '20060101', '20081231') + 1
order by orderdate;


-- 最终解决
select 
    DATEADD(day, nums.n - 1, '20060101') as orderdate
    , o.orderid
    , o.custid
    , o.empid
from
    dbo.nums
    left outer join sales.Orders as o
        on DATEADD(day, nums.n - 1, '20060101') = o.orderdate
where 
    nums.n <= DATEDIFF(day, '20060101', '20081231') + 1
order by 
    orderdate;

对外联接中非保留表的列值进行过滤：当检查涉及外联接的代码，查找逻辑错误时，应该检查的一个地方就是WHERE子句。如果WHERE子句中的条件是以<列><运算符><值>的形式引用了联接中的非保留表的列，这通常就是存在错误的一个标志。因为外联接得到的外部行中来自非保留表的列值均为null，而null<运算符><值>这种格式的表达式只会得到UNKNOWN。在where子句中，这样的查询条件会让所有的外部行都被过滤掉，效果上相当于抵消了外联接的作用。换句话说，这好像是把联接类型在逻辑上变成了内联接。所以对于这种情况，程序员要不就是选择错了联接类型，要不就是在查询条件上犯了错误。

参考下例：

select 
    c.custid
    , c.companyname
    , o.orderid
    , o.orderdate
from
    Sales.Customers as c
    left outer join Sales.Orders as o
        on c.custid = o.custid
where o.orderdate >= '20070101';

以上这条查询语句对customers表和orders表执行左联接操作。在应用where过滤条件之前，连接运算会返回内部行和外部行。对于所有外部行因为他们在o.orderdate列上取值都为null，所以where子句中条件o.orderdate >= '20070101'的计算结果为UNKNOWN，where子句会过滤掉所有的外部行。这意味着此处使用外部联接是徒劳的。程序员可能错误的使用了外联接，或者在where子句中指定了错误的查询条件。

在多表联接中使用外联接：在from子句中，表运算符在逻辑上是从左到右计算的。对外联接的处理顺序进行调整，可能会得到不同的输出结果，所以不能随意调整他们的顺序。
考虑下例：

select
    c.custid
    , o.orderid
    , od.productid
    , od.qty
from
    Sales.Customers as c
    left outer join Sales.Orders as o
        on c.custid = o.custid
    join Sales.orderdetails as od
        on o.orderid = od.orderid;

第一个联接是外联接，返回客户和他们的订单，其中包括没有下过任何订单的客户。外部行表示的是没有订单的客户。这些行和总与订单相关的列值为null。第二个联接是根据条件O.orderid=od.orderid，对orderdetails表中的订单条目和第一个联接的结果进行匹配；不过，在代表没有订单的客户的外部行中，o.orderid列的值为null。因此，联接条件在这些行的计算结果为UNKNOWN，这些行都会被过滤掉。
这个问题可以概括为：对于任何外联接，如果后面紧跟着一个内联接或右外联接，都会抵消掉外联接的外部行。当然，这一结论的前提是联接条件对来自联接左边的null值和联接右边的某些值进行了比较。如果想在输出结果中返回没有订单的客户，可以在第二个连接中也使用左外联接.

select
    c.custid
    , o.orderid
    , od.productid
    , od.qty
from
    Sales.Customers as c
    left outer join sales.Orders as o
        on c.custid = o.custid
    left outer join Sales.OrderDetails as od
        on o.orderid = od.orderid;

或者先用内联接来连接orders表和orderdetails表，然后再用右外联接来连接customers表。

select
    c.custid
    , o.orderid
    , od.productid
    , od.qty
from
    sales.orders as o
    join sales.OrderDetails as od
        on o.orderid = od.orderid
    right outer join Sales.Customers as c
        on c.custid = o.custid;

再或者可以把orders表和orderdetails表之间的内联用一对圆括号括起来，使其成为一个独立的逻辑处理语句。这样就可以在customers表和orders表与orderdetails的内联接结果之间再进行左外联接。

select 
    c.custid
    , o.orderid
    , od.productid
    , od.qty
from
    Sales.Customers as c
    left outer join
        (Sales.Orders as o
        join sales.orderdetails as od
            on o.orderid = od.orderid)
        on c.custid = o.custid;

随外联接一起使用count聚合函数：

另一个常见的逻辑错误与随外联接一起使用count聚合函数有关。当对外联接的结果进行分组，在使用count(*)聚合函数时，聚合操作会把内部行和外部行都计算在内，因为它只计算行数，而不管行内容。但通常都不希望把外部行业纳入计数考虑的范围内。
例如，以下查询原打算为每个客户返回各自的订单数量

select
    c.custid
    , count(*) as numorders
from
    Sales.Customers as c
    left outer join Sales.Orders as o
        on c.custid = o.custid
group by c.custid;

客户id为22和57的客户都显示为有一个订单，而实际上他们各自的订单数都为0，count(*)聚合函数不会检查某个行是否真的代表一个订单。为了解决这个问题，应该用count(<column>)来代替count(*)，并从联接的非保留表中选择一个列。这样一来count()聚合函数就会忽略外部行，因为这些行在计数列上的值为null。

select
    c.custid
    , count(o.orderid) as numorders
from
    Sales.Customers as c
    left outer join Sales.Orders as o
        on c.custid = o.custid
group by c.custid;