T-SQL之多联接查询

一.控制多联接的物理计算顺序

在编写查询语句时，经常会多次联接查询多个表。在没有外联接的多联接查询中，连接查询的先后顺序对最终的结果是无影响的，查询优化器知道这一点，所以在最终的执行计划中，优化器访问表的顺序可能跟查询语句中指定的联接顺序不一样。

例如，对于以下这样的一个多联接的查询：

use Northwind;
GO
select c.contactname,o.orderid,p.ProductName,s.ContactName from dbo.Customers c 
inner join dbo.Orders o on c.CustomerID=o.CustomerID
inner join dbo.[Order Details] od on od.OrderID = o.OrderID
inner join dbo.Products p on p.ProductID = od.ProductID
inner join dbo.Suppliers s on s.SupplierID = p.SupplierID

检查执行计划，你会发现，并没有按照查询语句中指定的逻辑顺序来物理地访问各张表。

如果您想最终的执行计划按照您在查询中指定的顺序来处理联接的话，有两种方法：

1.用FORCE ORDER提示选项。

use Northwind;
GO
select c.contactname,o.orderid,p.ProductName,s.ContactName from dbo.Customers c 
inner join dbo.Orders o on c.CustomerID=o.CustomerID 
inner join dbo.[Order Details] od on od.OrderID = o.OrderID 
inner join dbo.Products p on p.ProductID = od.ProductID
inner join dbo.Suppliers s on s.SupplierID = p.SupplierID option(force order)

这时再去看执行计划，就会发现跟访问顺序跟查询中的是一致的了。

2.可以执行SET FORCEPLAN。不建议用这种方式，它将影响会话中的所有查询。语法为：SET FORCEPLAN ｛ON|OFF｝

当然这种强制执行顺序的方法，是处理性能问题的最后一招。如果您对最优的联接顺序有很肯定的把握的话，可以使用提示选项的方式来处理，一般的情况下，还是建议让优化器去干这些事。

二.控制多联接的逻辑计算顺序

先看看下边这样的一个查询

use Northwind;
GO
select c.contactname,o.orderid,p.ProductName,s.ContactName from dbo.Customers c 
LEFT join dbo.Orders o on c.CustomerID=o.CustomerID
inner join dbo.[Order Details] od on od.OrderID = o.OrderID
inner join dbo.Products p on p.ProductID = od.ProductID
inner join dbo.Suppliers s on s.SupplierID = p.SupplierID

这个查询，目的是要返回所有客户的订单情况，注意这里包括没有订单的客户也要查询出来，理论上结果集行数2157行，其中有两个客户没有订单，其orderid等字段全部是null。可以执行了以上查询之后，却发现结果集中有2155行。为了方便查看，我们先加上where条件(where o.OrderID is null)。再执行这条查询，发现结果集为空。那么这条查询就有问题了，它没达到要求呀！为什么会出现这样的情况呢？下边来分析一下。

首先来看看最终的执行计划：

看见了没有！这里边的4个联接查询居然没有left join，全部是inner join！真凶就在这里！优化器把left join当成inner join处理了，当然就少了那两个没有订单的客户了。原来优化器是这么想的：在第一个查询中，我给你添加了外部行，这些外部行的orderid是NULL，而在后面的inner join中，这些为NULL的orderid是关联不到任何结果的。所以查询优化器就擅作主张了，既然第二个联接会消除第一个联接的外部行，那么我就干脆把第一个联接也当作inner join来处理了。所以，一个外连接后边跟着内联接并且内联接是针对外联接中的非保留表字段的话，这个外联接就无效了，直接当作内联接处理了。

问题找到了，就应该想办法去解决。

方法1:将后面的inner join 全部改成 left join。因为left join 可以将外部行一直保留在查询过程中的虚拟表中，所以最后的结果查询出来了正确的行数。但是如果存在没有相关订单明细的订单，或者没有相关产品的订单明细，或者没有相关供应商的产品，这个查询仍然会得到错误的结果，它会由于不匹配多个筛选条件而添加多个外部行。并且查询优化器不能对外联接进行联接排序优化，所以这种方法不可取。

方法2：修改连接顺序。这个查询的关键是，dbo.Customers表中有外部行，那就最后再关联它。于是有下边的查询：

use Northwind;
GO
select c.contactname,o.orderid,p.ProductName,s.ContactName from dbo.Orders o 
inner join dbo.[Order Details] od on od.OrderID = o.OrderID
inner join dbo.Products p on p.ProductID = od.ProductID
inner join dbo.Suppliers s on s.SupplierID = p.SupplierID
right join dbo.Customers c on c.CustomerID=o.CustomerID

看看执行计划，这个外联接就保留下来了，得到了正确的查询结果。

这个例子本身简单，而且业务也不复杂，所以容易修改逻辑，但是碰到比较复杂的查询，就不一定那么好修改了，更好的方法，请接着往下看。

方法3.ANSI SQL和T-SQL都是允许控制联接处理的逻辑顺序的。这里我们可以用加括号的方法，把第2，3，4个查询当作一个整体，让它们先联接查询，得出虚拟表，再和第一张表进行left join。代码如下：

use Northwind;
GO
select c.contactname,o.orderid,p.ProductName,s.ContactName from dbo.Customers c 
left join 
(
    dbo.Orders o 
    inner join dbo.[Order Details] od on od.OrderID = o.OrderID
    inner join dbo.Products p on p.ProductID = od.ProductID
    inner join dbo.Suppliers s on s.SupplierID = p.SupplierID
)
on c.CustomerID=o.CustomerID

这样看起来自然多了，符合一般的业务习惯了。另外说明一下，这里的括号不是必须的，但是强烈建议添加括号。理由很简单，提高可读性。

对于联接查询，还有很多地方需要去研究，先说到这里，以后深入了再细谈。小弟才疏学浅，如果有地方有纰漏，还请各位提出，指出问题，同时希望大家一起探讨开发中遇到的问题。

参考文献：《Microsoft SQL Server 2008 技术内幕：T-SQL查询》