程序将子查询转换为连接 [英] Procedurally transform subquery into join

问题描述

是否有将 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?

推荐答案

将子查询转换为 JOIN 可以非常简单:

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)

...可以转换为:

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

您的 JOIN 标准是您可以直接比较的地方.

Your JOIN criteria is where you have direct comparison.

但是当您查看 EXISTS 子句时，会出现一些复杂情况.EXISTS 是 通常是相关的，其中子查询由子查询外的表中的条件过滤.但 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)

...转换:

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

由于布尔值，结果集中存在更多行的风险.

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

这些应该总是改变，有偏见:

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 现在几乎在任何数据库上，Oracle 的 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

结论

我不确定这是否能满足您的排版需求，但希望我已经证明了关键是确定 JOIN 标准是什么.一旦您知道所涉及的列，您就知道所涉及的表.

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.

这篇关于程序将子查询转换为连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！