SQL Server 多表运算——联接查询

FROM子句再逻辑上时第一条要处理的子句，再其内则可以用表运算符对输入的表进行操作。SQL Server支持四种表运算符：JOIN、APPLY、PIVOT、UNPIVOT。JOIN表运算符时ANSI标准，而APPLY、PIVOT、UNPIVOT是T-SQL对标准的扩展。每种表运算符都以表作为输入，对表进行处理，经过一系列的逻辑查询处理阶段，最终返回一个结果表。

JOIN表运算符有三种基本类型：交叉联接、内连接和外联接。这三种联接的区别是它们采用的逻辑查询处理步骤各不相同，每种联接都有一套不同的步骤。交叉联接只有一个步骤——笛卡尔积；内联接有两个步骤——笛卡尔积、过滤；外联接有三个步骤——笛卡尔积、过滤、添加外部行。

 

一.交叉联接

在逻辑上，交叉联接是种最简单的联接，只实现一个逻辑查询步骤（笛卡尔积）。即将两个输入表的每行与另一个表的所有行进行匹配。如果个表有m行，而另个表有n行，将得到mXn行的结果集。

SELECT C. custid,E.empid
FROM Sales.Customers AS C
CROSS JOIN HR.Employees AS E;

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

SELECT 
El.empid, El.firstname,El.lastname, 
E2.empid, E2.firstname,E2.lastname 
FROM HR.Employees AS El 
CROSS JOIN HR.Employees AS E2; 

 

二.内联接

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

SELECT E.empid, E.firstname, E.lastname, o.orderid 
FROM HR.Employees AS E 
JOIN sales.orders AS O
ON E.empid = O.orderid

联接运算首先对两个表求笛卡尔积(9行员工记录X 830行订单记录=7 470行记录），然后根据条件E.empid=O.empid对行进行过滤， 最终返回830行。与WHERE和HAVING子句类似， ON子句同样也只返回令谓词结果为TRUE的行，而不会返回令谓词计算结果为 FALSE或UNKNOW的行。

一个联接表运算符只对两个表进行操作，而一条查询语句可以包含多个联接。通常，当 FROM子句中包含多个表运算符时，表运算符在逻辑上是按从左到右的顺序处理的。也就是说，第一个表运算符的结果表将作为第二个表运算符的输入，第二个表运算符的结果将作为第三个表运算符左边的输入，以此类推。

 

三.外联接

在外联接中，要把一个表标记为 “保留的” 表，可以在表名之间使用关键字LEFT OUTER JOIN、 RIGHT OUTER  JOIN, 以及FULL OUTER JOIN, 其中OUTER关键字是可选的。 LEFT关键字表示左边表的行是保留的，RIGHT关键字表示右边表的行是保留的，而FULL关键字则表示左右两边表的行都是保留的。外联接的第三个逻辑查询处理步骤就是要识别保留表中按照ON 条件在另一个表找不到与之匹配的那些行，再把这些行添加到联接的前两个步骤生成的结果表中 ； 对于来自联接的非保留表的那些列，追加的外部行中的这些列则用NULL作为占位符。

以下的查询根据客户的客户ID和订单的客户ID对Customers表和Orders表进行联接， 并返回客户和他们的订单信息。 该查询语句使用的联接类型是左外联接， 所以查询结果也会返回那些没有发出任何订单的客户：

SELECT C.custid, C.companyname, O.orderid 
FROM Sales.Customers AS C 
LEFT OUTER JOIN Sales.Orders AS O
ON C.custid = O.custid ;

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

假设现在只需要返回没有下任何订单的客户， 或者说只需要返回外部行。 为了解决这个问题， 可以在前面查询的基础上， 再加一个WHERE子句来只筛选外部行。 还记得对千外部行， 其来自联接的非保留表的列都标记为NULL吧。 所以可以只筛选联接的非保留表的列值之一为NULL的那些行， 如下所示：

SELECT E.empid, E.firstname, E.lastname, o.orderid 
FROM HR.Employees AS E 
JOIN sales.orders AS O
ON E.empid = O.orderid
WHERE O.orderid IS NULL; 

 当检查涉及外联接的代码， 查找逻辑错误时， 应该检查的一个地方就是WHERE子句。如果WHERE子句中的 条件是以＜ 列＞＜运算符＞＜值＞的形式引用了联接中非保留表的列，这通常就是存在错误的一个标志。 因为外联接得到的外部行中来自非保留表的列值均为 NULL. 而NULL<运算符＞＜值＞这种格式的表达式只会得到UNKNOWN (除非使用IS NULL运算符， 显式地查找NULL值）。 在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过滤条 件之前，联接运算会返回内部行（包含下过订单的客户）和外部行（包含没有下过订单的客户，与订单相关的列值为NULL)。对于所有的外部行，因为它们在O.orderdate列
上的取值都为NULL,所以WHERE子句中条件O.orderdate>='20070l01'的计算结果为UNKNOWN, WHERE子句会过滤掉所有的外部行。

 

四.外联接引申

•  表运算符在逻辑上是从左到右计算的。对外联接的处理顺序进行调整，可能会得到不同的输出结果， 所以不能随意调整它们的顺序。一些有趣的逻辑错误通常与外联接的逻辑处理顺序有关 。 假设现在要写一条多表联接查 询语句， 先对两个表执行外联接， 再和第三个表执行内联接。 如果在内联接 ON子句中的条件是对来自外联接非保留表的列和第三个表的列进行比较， 那么所有的外部行就会被过滤掉 。 记住， 对千外部行， 其来自联接非保留端的列值为NULL: 把NULL 和任何值比较， 得到的都是UNKNOWN: UNKNOWN都会被 ON条件过滤掉。 换句话说， 这样的查询条件将抵消外联接的作用， 在逻辑上好像查询使用的是内联接一样 。 例如， 以下查询语句：

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 。 第二个联接是根据条件0.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.Orderoetails 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.orderoetails AS OD 
ON o.orderid = OD.orderid 
RIGHT OUTER JOIN Sales.Customers AS C
ON O.custid = c.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.orderoetails AS OD
          ON O.orderid = OD.orderid) 
  ON c.custid = o.custid; 

•  当对外联接的结果进行分组，再使用COUNT(*)聚合函数时，聚合操作会把内部行和外部行都计算在内， 因为它只计算行数，而不管行的内容。但通常都不希望把外部行也纳入计数考虑的范围内。例如，以下查询原打算为每个客户返回各自的订单数量：

SELECT c.custid, COUNT(*) AS numorders 
FROM sales.customers AS c 
LEFT OUTER JOIN Sales.orders AS 0
ON c.custid = o.custid 
GROUP BY C.custid; 

但COUNT(*)聚合函数只统计行数，而不考虑这些行的含义或内容。对于没有下过订单的客户（例如，ID为22和57的客户），他们各自在联接结果中都有一个外部行，客户ID为22和57的客户都显示为有一个订单，而实际上他们各自的订单数都应该为0。为了解决这个问题，应该用COUNT(<column>)来代替COUNT(*),并从联接的非保留表中选择一个列。这样一来，COUNT()聚合函数就会忽略外部行， 因为这些行在计数列上的值为 NULL。 记住要选择只有在外部行中列值为NULL的列（例如， 订单的主键列orderid)。

 

 

好了，本篇文章就介绍到这儿，欢迎大家留言交流；喜欢或有帮助到您的话，点个赞或推荐支持一下！

 

转载于:https://www.cnblogs.com/johnvwan/p/9463432.html