联接（CROSS JOIN、JOIN、OUTER JOIN）

JOIN 表运算符对两个输入表进行操作。联接的类型有交叉联接、内部联接和外部联接，它们的区别在于如何应用逻辑查询处理阶段。交叉联接仅应用一个阶段——笛卡尔乘积，内部联接应用两个阶段——笛卡尔乘积和筛选，外部联接应用三个阶段——笛卡尔乘积、筛选和添加外部行。

交叉联接（CROSS JOIN）

交叉联接仅执行一个逻辑查询处理阶段——笛卡尔乘积，这一阶段对提供的两个输入表进行操作，联接并生成两个表的笛卡尔乘积（将一个输入表的每一行与另一个表的所有行匹配，得到m×n行）。

 -- 如果为表分配了别名，那么使用完整表名称作为前缀是无效的
 SELECT A.City, B.Company FROM Cities A CROSS JOIN Companies B  -- 推荐
 SELECT A.City, B.Company FROM Cities A, Companies B

自联接是指联接同一个表的多个实例，自联接被所有基本联接类型支持（交叉联接、内部联接、外部联接）。

 -- 自联接中必须为表指定别名，否则联接结果中所有列名都将不明确
 SELECT A.City, A.CityID, B.City, B.CityID FROM Cities A CROSS JOIN Cities B

交叉联接的一种用途是生成数字表（一个整数数列结果集）。

IF OBJECT_ID(N'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)

SELECT
    Num = A.digit * 100 + B.digit * 10 + C.digit + 1  -- 1~1000
FROM
    Digits A CROSS JOIN
    Digits B CROSS JOIN
    Digits C
ORDER BY Num

内部联接（INNER JOIN、JOIN）

内部联接应用两个逻辑查询处理阶段——首先对作为交叉联接的两个输入表应用一个笛卡尔乘积，然后按照指定的谓词筛选行。内部联接是在表名称之间指定 INNER JOIN 关键字。因为内部联接是默认链接，所以 INNER 关键字是可选的，可以仅指定 JOIN 关键字。指定的谓词（联接条件）用于在 ON 的子句中筛选行。

SELECT A.empid, A.firstname,A.lastname, B.orderid
FROM HR.Employees A JOIN Sales.Orders B ON A.empid=B.empid

联接的正确思考方式是基于关系代数，联接首先执行两个表的笛卡尔乘积，然后基于谓词筛选行。
内部联接可以在表名称之间指定一个逗号，然后在 WHERE 子句中指定联接条件：

SELECT A.empid, A.firstname,A.lastname, B.orderid
FROM HR.Employees A, Sales.Orders B WHERE A.empid=B.empid

但是，在复杂的具有多个表、筛选和其他查询元素的语句中，忘记在 WHERE 子句中指定联接条件的可能性会增加，这样可能就会误以为是一个交叉联接（无法区分是一个正确的交叉联接，还是一个错误的内部联接）。

其他

复合联接

复合联接是谓词涉及每侧多个属性的简单联接。

SELECT
    A.CityID, A.CreateTime, B.ProvinceID, B.Domain
FROM
    Test1 A JOIN Test2 B
    ON A.ProvinceID=B.ProvinceID AND A.CityID=B.CityID
WHERE A.CreateTime='20180801'

当联接条件仅涉及等号运算符时，联接称为相等联接。当联接条件涉及除等号外的任何运算符时，联接称为不等联接（Non-equi join）。

-- 一定要先考虑笛卡尔乘积
SELECT
    A.empid, A.firstname, A.lastname, B.empid, B.firstname, B.lastname
FROM HR.Employees A JOIN HR.Employees B ON A.empid<B.empid

多联接查询

联接表运算符仅操作两个表，但是单个查询可以有多个联接。当 FROM 子句中出现多个表运算符时，表运算符一般从左到右进行逻辑处理。第一个表运算符的结果表将被视为第二个表运算符的左侧输入，第二个表运算符的结果将被视为第三表运算符的左侧输入，以此类推。

SELECT
    A.custid, A.companyname, B.orderid, C.productid, C.qty
FROM
    Sales.Customers A JOIN
    Sales.Orders B ON A.custid=B.custid JOIN
    Sales.OrderDetails C ON B.orderid=C.orderid

外部联接（OUTER JOIN）

外部联接应用三个逻辑查询处理阶段——笛卡尔乘积、ON 筛选和添加外部行。外联接包括 LEFT OUTER JOIN、RIGHT OUTER JOIN 和 FULL OUTER JOIN，OUTER 关键字是可选的。外部联接的第三个逻辑查询处理阶段识别保留表中基于 ON 谓词未能与另一个表匹配的行，添加这些行到前两个联接阶段生成的结果表中，并对于联接非保留侧的属性使用 NULL 作为占位符。

SELECT A.custid, A.companyname, B.orderid
FROM Sales.Customers A LEFT JOIN Sales.Orders B on A.custid=B.custid

针对外部联接的保留侧，可以分为内部行和外部行。内部行时基于谓词与另一侧匹配的行，外部行则是未匹配的行。内部联接仅返回内部行，而外部联接返回内部行和外部行。所以以下两种方式都可以返回内部行结果：

-- WHERE 筛选出内部行
SELECT A.custid, A.companyname, B.orderid
FROM Sales.Customers A LEFT JOIN Sales.Orders B on A.custid=B.custid
WHERE B.orderid IS NOT NULL  -- 因为添加的外部行用 NULL 作为占位符

SELECT A.custid, A.companyname, B.orderid
FROM Sales.Customers A INNER JOIN Sales.Orders B on A.custid=B.custid

外部联接及数字表的应用

1. 包含缺失值
   在查询数据时，可以使用外部联接标识和包含缺失值。要求确保在2006.1.1日到2008.12.31日中每天至少有一行输出，对于范围内具有的日期不做任何操作，但希望输出包含没有的日期，使用 NULL 标记作为占位符。

   -- 首先通过数字表获取需要查询范围的完整日期列表
   SELECT orderdate=DATEADD(D, A.n, '20051231')
   FROM dbo.Nums A
   WHERE A.n<DATEDIFF(D, '20051231', '20090101') ORDER BY orderdate
   
   -- 然后通过左外联接添加外部行
   SELECT
       orderdate=DATEADD(D, A.n, '20051231'), B.orderid, B.custid, B.empid
   FROM
       dbo.Nums A LEFT JOIN
       Sales.Orders B ON DATEADD(D, A.n, '20051231')=B.orderdate
   WHERE A.n<DATEDIFF(D, '20051231', '20090101') ORDER BY orderdate