有一个广义的程序或算法，用于将一个SQL子查询转换成一个连接，或者相反？也就是说，有一组可应用于包含一个子查询，结果在一个功能相当的声明中没有一个子查询语法正确的SQL查询语句的印刷业务？如果是的话，它们是什么（比如，什么是算法），以及在什么情况下，他们不适用？

Is there a generalized procedure or algorithm for transforming a SQL subquery into a join, or vice versa? That is, is there a set of typographic operations that can be applied to a syntactically correct SQL query statement containing a subquery that results in a functionally equivalent statement without a subquery? If so, what are they (i.e., what's the algorithm), and in what cases do they not apply?

推荐答案

转换一个子查询转换成一个连接可以是pretty的简单：

Converting a subquery into a JOIN can be pretty straightforward:

 FROM TABLE_X x
WHERE x.col IN (SELECT y.col FROM TABLE_Y y)

...可以转换为：

...can be converted to:

FROM TABLE_X x
JOIN TABLE_Y y ON y.col = x.col

您的联接条件是，你必须直接比较。

Your JOIN criteria is where you have direct comparison.

但有并发症，当你在看 EXISTS 条款。 EXISTS的是的典型correllated，子查询被从桌子查询外的条件筛选。但EXISTS仅用于返回基于标准的一个布尔值。

But there are complications when you look at the EXISTS clause. EXISTS are typically correllated, where the subquery is filtered by criteria from the table(s) outside the subquery. But the EXISTS is only for returning a boolean based on the criteria.

 FROM TABLE_X x
WHERE EXISTS (SELECT NULL
                FROM TABLE_Y y
               WHERE y.col = x.col)

...转换：

...converted:

FROM TABLE_X x
JOIN TABLE_Y y ON y.col = x.col

由于布尔的，还有更多的行ResultSet中转向了风险。

Because of the boolean, there's a risk of more rows turning up in the resultset.

这些应该的总是的改变，是 prejudice

These should always be changed, with prejudice:

SELECT x.*,
       (SELECT MAX(y.example_col)
          FROM TABLE_Y y
         WHERE y.col = x.col)
  FROM TABLE_X x

你可能注意到一个图案了，但我做了这个一个内嵌视图例子有一点不同：

You're probably noticing a patter now, but I made this a little different for an inline view example:

SELECT x.*,
       z.mc
  FROM TABLE_X x
  JOIN (SELECT y.col, --inline view within the brackets
               MAX(y.example_col) 'mc'
          FROM TABLE_Y y
      GROUP BY y.col) z ON z.col = x.col

的关键是确保线视图结果集包括加入到所需要的列（多个），随着柱

The key is making sure the inline view resultset includes the column(s) needed to join to, along with the columns.

您可能已经注意到，我没有任何LEFT JOIN的例子 - 这只会是必要的，如果从子查询中使用NULL测试柱（ COALESCE 在几乎所有的数据库这些天来，甲骨文公司 NVL 或 NVL2 ，MySQLs IFNULL ， SQL Server的 ISNULL ，等...）：

You might've noticed I didn't have any LEFT JOIN examples - this would only be necessary if columns from the subquery use NULL testing (COALESCE on almost any db these days, Oracle's NVL or NVL2, MySQLs IFNULL, SQL Server's ISNULL, etc...):

SELECT x.*,
       COALESCE((SELECT MAX(y.example_col)
          FROM TABLE_Y y
         WHERE y.col = x.col), 0)
  FROM TABLE_X x

转换：

   SELECT x.*,
          COALESCE(z.mc, 0)
     FROM TABLE_X x
LEFT JOIN (SELECT y.col,
                  MAX(y.example_col) 'mc'
             FROM TABLE_Y y
         GROUP BY y.col) z ON z.col = x.col

结论

我不知道是否会满足您的印刷需求，但希望我已经证明，关键是确定哪些连接标准的。一旦你知道所涉及的列（S），你知道表（县）参与。

Conclusion

I'm not sure if that will satisfy your typographic needs, but hope I've demonstrated that the key is determining what the JOIN criteria is. Once you know the column(s) involved, you know the table(s) involved.