将子查询重写为联接

目录

SQL谜题

子查询与内部连接答案

子查询与内连接——转换查询

哪个更容易阅读？

子查询还是内部联接？哪个更高效？

这篇文章讨论了几种类型的子查询，以及如何将每个子查询切换到另一种形式，例如连接。

在这个谜题中，我们将学习如何使用内连接重写子查询。了解子查询与内部联接可以帮助您解决面试问题和性能问题。尽管子查询具有独特的能力，但有时最好使用其他SQL构造，例如join。

通过阅读本文，您将了解几种类型的子查询，以及如何将每个子查询切换为另一种形式，例如join。

解决难题是学习SQL的好方法。没有什么比练习你所学的更重要的了。一旦你弄清楚了这个谜题，请在评论中发布你的答案，以便我们大家互相学习。

SQL谜题

查询混乱的补救措施……

一位同事刚刚了解了子查询并编写了一些SQL来从AdventureWorks数据库中检索员工姓名和生日。问题是，他们想改变它，现在很难阅读！

你能帮助他们简化下面的SQL吗？

SELECT   E.HireDate,
         (SELECT FirstName
          FROM   Person.Person P1
          WHERE  P1.BusinessEntityID = E.BusinessEntityID),
         (SELECT LastName
          FROM   Person.Person P2
          WHERE  P2.BusinessEntityID = E.BusinessEntityID),
         E.BirthDate
FROM     HumanResources.Employee E
WHERE    (SELECT PersonType
          FROM   Person.Person T
          WHERE  T.BusinessEntityID = E.BusinessEntityID) = 'EM'
ORDER BY HireDate,
         (SELECT FirstName
          FROM   Person.Person P1
          WHERE  P1.BusinessEntityID = E.BusinessEntityID)

你会写什么语句来使它更容易阅读，并且可能更有效地运行？

子查询与内部连接答案

在我们开始之前，让我们谈谈现有的查询……它是什么？

您将看到查询组合了来自两个不同表的数据。它在FROM和WHERE子句中使用子查询来做到这一点。

SELECT   E.HireDate,
         (SELECT FirstName
          FROM   Person.Person P1
          WHERE  P1.BusinessEntityID = E.BusinessEntityID),
         (SELECT LastName
          FROM   Person.Person P2
          WHERE  P2.BusinessEntityID = E.BusinessEntityID),
         E.BirthDate
FROM     HumanResources.Employee E
WHERE    (SELECT PersonType
          FROM   Person.Person T
          WHERE  T.BusinessEntityID = E.BusinessEntityID) = 'EM'
ORDER BY HireDate,
         (SELECT FirstName
          FROM   Person.Person P1
          WHERE  P1.BusinessEntityID = E.BusinessEntityID)

此外，您会看到每个子查询的WHERE子句都将返回的行限制为等于Employee.BusinessEntityID。这就是人们所说的关联子查询。

我还想指出FROM中的查询必须返回单个值(标量)。如果他们不这样做，则会引发错误。

可以想象，这很危险，因为很难保证查询最多返回一行。我知道在这种情况下是安全的，因为匹配条件发生在每个表的主键之间。

子查询与内连接——转换查询

如果我正在编写此查询，我将使用INNER JOIN。这是我要写的查询：

SELECT   E.HireDate,
         P.FirstName,
         P.LastName,
         E.BirthDate
FROM     HumanResources.Employee E
         INNER JOIN Person.Person P
         ON P.BusinessEntityID = E.BusinessEntityID
WHERE    P.PersonType  = 'EM'
ORDER BY E.HireDate,
         P.FirstName

哪个更容易阅读？

这不应该是一场争论，INNER JOIN它要短得多，我认为这一点很重要。该join子句不言自明。您知道它将两个表关联在一起；而对于子查询，它是如此明显。

该INNER JOIN版本更易于维护。

此外，使用子查询方法，您会看到很多代码重复。现在这似乎没什么大不了的，但是如果您必须更改查询，那就是，就像现在当您进行更改时，您需要确保在多个位置进行相同的更改。

子查询还是内部联接？哪个更高效？

下面是子查询版本的查询计划：

我已经强调了四个子查询的效果。对于此语句，每个查询都会导致一个嵌套循环。这些都不好。

这意味着如果您在两个表中各有10行，那么您需要平均遍历第二个表50次(100/2)以查找第一个表中的每一行以找到匹配项。这意味着，查找匹配可能需要100 * 50 = 500次以上的查找，而不是使用两个或三个操作来查找匹配项。

嵌套循环是生活中的事实，但越少越好。

这是INNER JOIN版本：

SET SHOWPLAN_ALL ON
SELECT   E.HireDate,
         P.FirstName,
         P.LastName,
         E.BirthDate
FROM     HumanResources.Employee E
         INNER JOIN Person.Person P
         ON P.BusinessEntityID = E.BusinessEntityID
WHERE    P.PersonType  = 'EM'
ORDER BY E.HireDate,
         P.FirstName

在这里，您会看到只有一个嵌套循环。当然，这比四个好。

在观察每组语句的SQL和查询计划之后，您可以看到INNER JOIN在几个方面是优越的；但是，请查看简化的计划！

查询的真正任务是合并两个表中的列；这就是INNER JOINS擅长的。当然，有时间子查询是有意义的，并且可以用来做你不能用连接做的事情，但是在这种情况下，使用一个是没有意义的。

本文最初发布于Rewrite Subquery as a Join - Essential SQL

https://www.codeproject.com/Articles/5326917/Rewrite-Subquery-as-a-Join