以程序方式将子查询转换为连接 [英] 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、MySQL 的 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屋！