SQL 联接查询 总结

一、联接分类

内部联接（典型的联接运算，使用类似于 = 或 <> 的比较运算符）。内部联接包括同等联接和自然联接。
内部联接通过比较相等的值的字段(被连接的表所共同拥有的)，将匹配的行作为结果集(即将两个表都满足条件的记录作为结果集)。

    A:内连接是SQLServer默认的连接方式，可以把INNERJOIN简写成JOIN

    B:连接的条件中不要指定空值，因为空值和其他值都不会相等

外部联接 外部联接可以是左向外部联接、右向外部联接或完整外部联接。

      外部联接会返回FROM 子句中提到的至少一个表或视图中的所有行，只要这些行符合任何 WHERE 或 HAVING 搜索条件。将检索通过左外部联接引用的左表中的所有行，以及通过右外部联接引用的右表中的所有行。在完全外部联接中，将返回两个表的所有行。

（1）左外联接 ：返回左表的所有满足where或having指定条件的所有记录，右表中没有匹配的记录时补充为NULL。

       LEFTJOIN 等价于 LEFT OUTER JOIN。

（2）右外联接：返回右表中所有满足where或 having指定条件的所有记录，左表中没有匹配的记录时补充为NULL。

       RIGHT JOIN 或RIGHT OUTER JOIN
左外连接和右外联接是互为反向联接，左外连接和右外联接中表的顺序不能交换位置，否则改变连接方向。

（3）全联接：返回左表和右表的所有满足条件的记录（即结果集为左外联接和右外联接的并集）。

       FULL JOIN 等价于 FULL OUTER JOIN
（4）交叉联接： 返两个表的笛卡尔积（左表中的每一行都与右表的所有行进行组合）。（结果集的记录条数为两个表记录数之积）。
     CROSSJOIN

二、联接的限制

（1）可以在FROM 或 WHERE 子句中指定内部联接；但只能在 FROM 子句中指定外部联接。

（2）联接条件与WHERE 和 HAVING 搜索条件相结合，用于控制从 FROM 子句所引用的基表中选定的行。

（3）在FROM 子句中指定联接条件有助于将这些联接条件与 WHERE 子句中可能指定的其他任何搜索条件分开， SQL-92 FROM 子句联接语法如下：

FROM first_table join_type second_table [ON (join_condition)]

join_type 指定要执行的联接类型：内部联接、外部联接或交叉联接。join_condition 定义用于对每一对联接行进行求值的谓词。

（4）联接条件中的列不必具有相同的数据类型和相同的数据类型，但数据类型必须兼容（即可以隐式转换）。如果数据类型不兼容则必须进行显式转换。

（5）不能在 ntext、text 或 image 列上直接联接表。但可以使用SUBSTRING 在 ntext、text 或 image 列上间接联接表 

（6）内联接中 任何一个表中满足条件 有NULL的记录都不会返回（联接表的列中的空值（如果有）互相不匹配，如果其中一个联接表的列中出现空值，只能通过外部联接返回这些空值）

下例来自 MSDN

table1                          table2
a           b                   c            d
-------     ------              -------      ------
      1        one                 NULL         two
   NULL      three                   4        four
      4      join4

将列 a 中的值与列 c 中的值进行比较的联接不能获得包含 NULL 值的列的匹配结果：

SELECT*
FROM table1 t1 JOIN table2 t2
   ON t1.a = t2.c
ORDER BY t1.a

而是只返回列a 和列 c 中值为 4 的一行：

a           b      c           d     
----------- ------ ----------- ------
4           join4  4          four  

(1 row(s) affected)

另外，从基表返回的空值与从外部联接返回的空值很难区分开。例如，下面的 SELECT 语句对这两个表进行左向外部联接：

SELECT*
FROM table1 t1 LEFT OUTER JOIN table2 t2
   ON t1.a = t2.c
ORDER BY t1.a

下面是结果集： 

a           b      c           d     
----------- ------ ----------- ------
NULL        three  NULL       NULL
1           one    NULL       NULL
4           join4  4          four  

三、 SQL Server 处理联接的逻辑顺序：

查询引擎会从多种可行的方法中选择最有效的方法来处理联接，各种联接的实际执行过程会采用多种不同的优化方式，但是逻辑顺序均为：

应用 FROM 子句中的联接条件。

应用 WHERE 子句中的联接条件和搜索条件。

应用 HAVING 子句中的搜索条件。

四、举例　来自ＭＳＤＮ

 左外部联接。包括所有商品

USEAdventureWorks;

GO

SELECTp.Name, pr.ProductReviewID

FROMProduction.Product p

LEFTOUTER JOIN Production.ProductReview pr

ONp.ProductID = pr.ProductID

不管是否与 ProductReview 表的 ProductID 列相匹配，LEFT OUTER JOIN 都会在结果中包括 Product 表的所有行。请注意，对于结果中没有匹配的产品审核 ID 的产品，行的 ProductReviewID 列中则包含一个空值。

右外部联接：要在结果中包括所有销售人员，不论是否为他们分配了区域

USEAdventureWorks;

GO

SELECTst.Name AS Territory, sp.SalesPersonID

FROMSales.SalesTerritory st

RIGHTOUTER JOIN Sales.SalesPerson sp

ONst.TerritoryID = sp.TerritoryID ;

全外联接以下查询只返回没有匹配销售订单的产品以及没有匹配产品的销售订单（虽然本例中所有销售订单都有匹配的产品）。

USEAdventureWorks;
GO
-- The OUTER keyword following the FULL keyword is optional.
SELECT p.Name, sod.SalesOrderID
FROM Production.Product p
FULL OUTER JOIN Sales.SalesOrderDetail sod
ON p.ProductID = sod.ProductID
WHERE p.ProductID IS NULL
OR sod.ProductID IS NULL
ORDER BY p.Name ;