sql - 简化 SQL 语句的一般规则

Question

我正在寻找一些“推理规则”(类似于设置操作规则或逻辑规则)，我可以使用它们来减少 SQL 查询的复杂性或大小。有这样的东西存在吗？有什么文件，有什么工具吗？您自己发现了什么等价物吗？它在某种程度上类似于查询优化，但不是在性能方面。

换句话来说:使用 JOIN、SUBSELECT、UNION 进行(复杂)查询是否可以(或不可以)通过使用一些转换规则将其简化为更简单、等效的 SQL 语句，该语句产生相同的结果？

因此，我正在寻找 SQL 语句的等效转换，例如大多数 SUBSELECT 可以重写为 JOIN。

Answer 1

To state it different: Having a (complex) query with JOINs, SUBSELECTs, UNIONs is it possible (or not) to reduce it to a simpler, equivalent SQL statement, which is producing the same result, by using some transformation rules?

<小时/>

这个答案写于 2009 年。这里描述的一些查询优化技巧现在已经过时，其他技巧可以提高效率，但其他技巧仍然适用。有关不同数据库系统功能支持的声明适用于撰写本文时已存在的版本。

<小时/>

这正是优化器的谋生之道(我并不是说他们总是做得很好)。

由于 SQL 是一种基于集合的语言，因此通常有多种方法将一个查询转换为另一个查询。

喜欢这个查询:

SELECT  *
FROM    mytable
WHERE   col1 > @value1 OR col2 < @value2

可以改成这样(前提是mytable有主键):

SELECT  *
FROM    mytable
WHERE   col1 > @value1
UNION
SELECT  *
FROM    mytable
WHERE   col2 < @value2

或者这个:

SELECT  mo.*
FROM    (
        SELECT  id
        FROM    mytable
        WHERE   col1 > @value1
        UNION
        SELECT  id
        FROM    mytable
        WHERE   col2 < @value2
        ) mi
JOIN    mytable mo
ON      mo.id = mi.id

，看起来更丑，但可以产生更好的执行计划。

最常见的事情之一就是替换此查询:

SELECT  *
FROM    mytable
WHERE   col IN
        (
        SELECT  othercol
        FROM    othertable
        )

这个:

SELECT  *
FROM    mytable mo
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    othertable o
        WHERE   o.othercol = mo.col
        )

在某些 RDBMS(例如 PostgreSQL 8.4)中，DISTINCT和GROUP BY使用不同的执行计划，因此有时最好用另一种替换一个:

SELECT  mo.grouper,
        (
        SELECT  SUM(col)
        FROM    mytable mi
        WHERE   mi.grouper = mo.grouper
        )
FROM    (
        SELECT  DISTINCT grouper
        FROM    mytable
        ) mo

对比

SELECT  mo.grouper, SUM(col)
FROM    mytable
GROUP BY
        mo.grouper

在 PostgreSQL 中，DISTINCT排序和 GROUP BY哈希值。

MySQL 5.6 缺少 FULL OUTER JOIN ，因此可以重写如下:

SELECT  t1.col1, t2.col2
FROM    table1 t1
LEFT OUTER JOIN
        table2 t2
ON      t1.id = t2.id

对比

SELECT  t1.col1, t2.col2
FROM    table1 t1
LEFT JOIN
        table2 t2
ON      t1.id = t2.id
UNION ALL
SELECT  NULL, t2.col2
FROM    table1 t1
RIGHT JOIN
        table2 t2
ON      t1.id = t2.id
WHERE   t1.id IS NULL

，但请参阅我的博客中的这篇文章，了解如何在 MySQL 中更有效地执行此操作:

• Emulating FULL OUTER JOIN in MySQL

Oracle 11g 中的分层查询:

SELECT  DISTINCT(animal_id) AS animal_id
FROM    animal
START WITH
        animal_id = :id
CONNECT BY
        PRIOR animal_id IN (father, mother)
ORDER BY
        animal_id

可以改成这样:

SELECT  DISTINCT(animal_id) AS animal_id
FROM    (
        SELECT  0 AS gender, animal_id, father AS parent
        FROM    animal
        UNION ALL
        SELECT  1, animal_id, mother
        FROM    animal
        )
START WITH
        animal_id = :id
CONNECT BY
        parent = PRIOR animal_id
ORDER BY
        animal_id

，后者效率更高。

有关执行计划的详细信息，请参阅我博客中的这篇文章:

• Genealogy query on both parents

要查找与给定范围重叠的所有范围，您可以使用以下查询:

SELECT  *
FROM    ranges
WHERE   end_date >= @start
        AND start_date <= @end

，但在 SQL Server 中，这种更复杂的查询会更快地产生相同的结果:

SELECT  *
FROM    ranges
WHERE   (start_date > @start AND start_date <= @end)
        OR (@start BETWEEN start_date AND end_date)

，不管你相信与否，我的博客中也有一篇关于此的文章:

• Overlapping ranges: SQL Server

SQL Server 2008 还缺乏一种有效的方法来执行累积聚合，因此此查询:

SELECT  mi.id, SUM(mo.value) AS running_sum
FROM    mytable mi
JOIN    mytable mo
ON      mo.id <= mi.id
GROUP BY
        mi.id

可以使用游标更有效地重写(上帝帮助我，你没听错:“游标”、“更高效”和“SQL Server”合而为一)。

请参阅我博客中的这篇文章，了解如何操作:

• Flattening timespans: SQL Server

有一种在金融应用程序中常见的查询，它可以获取货币的有效汇率，就像 Oracle 11g 中的这样一个查询:

SELECT  TO_CHAR(SUM(xac_amount * rte_rate), 'FM999G999G999G999G999G999D999999')
FROM    t_transaction x
JOIN    t_rate r
ON      (rte_currency, rte_date) IN
        (
        SELECT  xac_currency, MAX(rte_date)
        FROM    t_rate
        WHERE   rte_currency = xac_currency
                AND rte_date <= xac_date
        )

可以对该查询进行大量重写，以使用允许 HASH JOIN 的相等条件。而不是NESTED LOOPS :

WITH v_rate AS
        (
        SELECT  cur_id AS eff_currency, dte_date AS eff_date, rte_rate AS eff_rate
        FROM    (
                SELECT  cur_id, dte_date,
                        (
                        SELECT  MAX(rte_date)
                        FROM    t_rate ri
                        WHERE   rte_currency = cur_id
                                AND rte_date <= dte_date
                        ) AS rte_effdate
                FROM    (
                        SELECT  (
                                SELECT  MAX(rte_date)
                                FROM    t_rate
                                ) - level + 1 AS dte_date
                        FROM    dual
                        CONNECT BY
                                level <=
                                (
                                SELECT  MAX(rte_date) - MIN(rte_date)
                                FROM    t_rate
                                )
                        ) v_date,
                        (
                        SELECT  1 AS cur_id
                        FROM    dual
                        UNION ALL
                        SELECT  2 AS cur_id
                        FROM    dual
                        ) v_currency
                ) v_eff
        LEFT JOIN
                t_rate
        ON      rte_currency = cur_id
                AND rte_date = rte_effdate
        )
SELECT  TO_CHAR(SUM(xac_amount * eff_rate), 'FM999G999G999G999G999G999D999999')
FROM    (
        SELECT  xac_currency, TRUNC(xac_date) AS xac_date, SUM(xac_amount) AS xac_amount, COUNT(*) AS cnt
        FROM    t_transaction x
        GROUP BY
                xac_currency, TRUNC(xac_date)
        )
JOIN    v_rate
ON      eff_currency = xac_currency
        AND eff_date = xac_date

尽管后一个查询非常庞大，但速度却快了六倍。

这里的主要思想是替换 <=与 = ，这需要构建一个内存中的日历表来连接。

• Converting currencies