作为子查询中的关键字,IN及EXISTS在许多情况下都会被使用到,它们的功能通常可以转化为INNER JOIN。在执行计划中,IN关键字一般会被直接转换成连接操作符,并且依据不同的数据量,选择采用NESTED LOOP或HASH等操作。简单给个示例。查询所有受雇佣人的个人信息,雇佣信息在Employee表中,个人信息在Person表中,通过表中的BusinessEntityId字段关联两个表。为了使语句采用Nested Loop的方式进行inner join关联,选择使用IN/EXISTS子句。如代码清单3-8所示,两种不同的语句编写方法(Query1及Query2),得出的具体执行如图3-7所示,可以看到,它们是一样的。
代码清单3-8 IN、EXISTS示例代码
--Query1 SELECT * FROM Person.Person AS p WITH(NOLOCK) WHERE p.BusinessEntityID IN ( SELECT e.BusinessEntityID FROM HumanResources.Employee AS e ) --Query2 SELECT * FROM Person.Person AS p WITH(NOLOCK) WHERE EXISTS ( SELECT 1 FROM HumanResources.Employee AS e WHERE e.BusinessEntityID=p.BusinessEntityID )
LEFT JOIN主要用于转换“非”操作的数据,例如NOT IN或NOT EXISTS,同样地,查询优化器会对这些NOT进行比较,并转换成LEFT JOIN的方式。以下代码中的几个语句,其执行计划也是相同的,如图3-8所示。
SELECT * FROM Person.Person AS p WHERE NOT EXISTS (SELECT 1 FROM Person.PersonPhone AS pp WHERE pp.BusinessEntityID=p.BusinessEntityID ) SELECT * FROM Person.Person AS p WHERE p.BusinessEntityID NOT IN ( SELECT pp.BusinessEntityID FROM Person.PersonPhone AS pp ) SELECT * FROM Person.Person AS p LEFT JOIN Person.PersonPhone AS pp ON p.BusinessEntityID = pp.BusinessEntityID WHERE pp.BusinessEntityID IS NULL