为简化SQL语句的一般规则(General rules for simplifying SQL st

我在寻找一些“推理规则”（类似设置操作规则或逻辑规则），我可以用它来降低复杂性或大小的SQL查询。 是否存在这样的事情？ 任何文件，任何工具？ 你对自己找到的任何换算公式？ 这在某种程度上类似于查询优化，而不是在性能方面。

为了不同的状态吧：经与联接，子查询，工会（复）查询是否有可能（或不）将其降低到一个更简单，相当于SQL语句，它是生产相同的结果，通过使用一些转换规则？

所以，我在找喜欢的事实，大多数子查询可以重写为连接的SQL语句的等价变换。

Answer 1:

为了不同的状态吧：经与联接，子查询，工会（复）查询是否有可能（或不）将其降低到一个更简单，相当于SQL语句，它是生产相同的结果，通过使用一些转换规则？

这正是优化为生（不是我说的，他们总是这样做很好）做的。

由于SQL是基于集合的语言，通常有一个查询转换为其他的方法不止一种。

像这样的查询：

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

可以转化成这样的：

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 ）， 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缺乏FULL OUTER JOIN ，因此它可以被改写为folloing：

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 ：

• 仿效FULL OUTER JOIN在MySQL

在这个层次查询Oracle ：

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

，后者是更高性能的。

看到这篇文章在我的博客的执行计划的详细信息：

• 父母双方家谱查询

要寻找重叠的给定范围内的所有范围，可以使用下面的查询：

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)

，并相信与否，我在我的博客上这样的文章太：

• 重叠范围：SQL服务器

SQL Server还缺乏一个有效的方式做到累积的集合体，所以这个查询：

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

可以使用，主帮助我更有效地重写，游标（你听我的权利： cursors ， more efficiently和SQL Server一句话）。

请参阅本文中我对如何做博客：

• 扁平化的时间跨度：SQL服务器

有某一种，用于有效率达搜索的货币，像这样的金融应用中通常遇到的查询Oracle ：

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

尽管是笨重的地狱，后者查询是6快倍。

这里的主要思想是取代<=带= ，这就要求建立一个内存中的日历表。 到JOIN用。

• 货币转换

Answer 2:

这里有几个来自与Oracle 8和9的工作（当然，有时做相反可能使查询更简单或更快）：

括号可以，如果他们不用于覆盖运算符优先级被删除。 一个简单的例子是，当所有的在布尔运算符where子句是相同的： where ((a or b) or c)等同于where a or b or c 。

一个子查询可以经常（如果不总是） 与简化它的主查询合并 。 根据我的经验，这往往可以提高性能显着：

select foo.a,
       bar.a
  from foomatic  foo,
       bartastic bar
 where foo.id = bar.id and
       bar.id = (
         select ban.id
           from bantabulous ban
          where ban.bandana = 42
       )
;

相当于

select foo.a,
       bar.a
  from foomatic    foo,
       bartastic   bar,
       bantabulous ban
 where foo.id = bar.id and
       bar.id = ban.id and
       ban.bandana = 42
;

使用ANSI联接 WHERE子句中的真正有趣的部分分离出来很多“代码猴子”的逻辑：以前的查询相当于

select foo.a,
       bar.a
  from foomatic    foo
  join bartastic   bar on bar.id = foo.id
  join bantabulous ban on ban.id = bar.id
 where ban.bandana = 42
;

如果您要检查行的存在，不使用count（*），而是使用任何rownum = 1或将查询在where exists子句仅取一排，而不是全部。

Answer 3:

• 我想了明显的是寻找一个可以与SQL“设置”根据操作者更换的光标。
• 接下来我的名单上，是寻找一个可以重新写成一个不相关的查询任何相关子查询
• 在长期存储过程，打破了单独的SQL语句到自己的存储过程。 这样，他们会得到有自己的缓存查询计划。
• 寻找那些可以有自己的范围缩短交易。 我经常发现，可以安全地之外的事务中的语句。
• 子选择通常可以重新写为直线前进加入（现代优化器善于发现那些简单）

作为@Quassnoi提到的，​​优化工具往往做得很好。 帮助它的方法之一是确保指标和统计数据是最新的，并为您的查询工作负载存在，合适的索引。

Answer 4:

我想以取代连接查询所有类型的子选择。

这一个是显而易见的：

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

通过

SELECT  mo.*
FROM    mytable mo inner join othertable o on o.othercol = mo.col

而这一次低估：

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

通过

SELECT  mo.*
FROM    mytable mo left outer join othertable o on o.othercol = mo.col
WHERE   o.othercol is null

它可以帮助数据库管理系统选择在一个大的请求良好的执行计划。

Answer 5:

我喜欢在一个团队中每个人都遵循一套标准，以使代码可读性，可维护性，易于理解，耐水洗等。:)

• 每个人都使用相同的别名
• 没有游标。 没有循环
• 为什么连想的时候可以EXISTS
• INDENT
• 一致性编码风格

这里存在一些更多的东西有哪些你最有用的数据库标准是什么？

Answer 6:

由于SQL的性质，你绝对必须要知道的任何重构的性能影响。 重构SQL应用程序是一个沉重强调性能重构一个很好的资源（见第5章）。

Answer 7:

虽然简化可能不等于优化，简化，可以以书面可读SQL代码，这又将是能够检查你的SQL代码为概念的正确性至关重要的（不是语法正确，你的开发环境应该检查你）很重要。 在我看来，在一个理想的世界，我们会写最简单的，可读的SQL代码，然后优化器将重写SQL代码，以任何形式（也许更详细）会跑的最快。

我发现SQL语句的这种思维的基础上设定的逻辑是非常有用的，特别是如果我需要在那里合并条款或找出一个where子句的复杂否定。 我用的是布尔代数的法律在这种情况下。

为简化最重要的where子句可能是德摩根定律（注意，“·”是“与”和“+”是“或”）：

• NOT（X·Y）= NOT X + NOTÿ
• NOT（X + Y）= X不·NOTÿ

这意味着在SQL中：

NOT (expr1 AND expr2) -> NOT expr1 OR NOT expr2
NOT (expr1 OR expr2) -> NOT expr1 AND NOT expr2

这些法律可以在简化非常有用的地方，有很多的嵌套的条款AND和OR部分。

这也是有用的记得声明field1 IN (value1, value2, ...)等同于field1 = value1 OR field1 = value2 OR ... 。 这使您可以否定IN ()两种方法之一：

NOT field1 IN (value1, value2)  -- for longer lists
NOT field1 = value1 AND NOT field1 = value2  -- for shorter lists

一个子查询可以被视为这种方式也。 例如，这种否定where子句：

NOT (table1.field1 = value1 AND EXISTS (SELECT * FROM table2 WHERE table1.field1 = table2.field2))

可改写为：

NOT table1.field1 = value1 OR NOT EXISTS (SELECT * FROM table2 WHERE table1.field1 = table2.field2))

这些法律不会告诉你如何使用连接到使用子查询的SQL查询转换为一个，但布尔逻辑可以帮助您了解连接类型和你的查询应该返回。 例如，具有表A和B ，一个INNER JOIN是像A AND B ，一个LEFT OUTER JOIN是像(A AND NOT B) OR (A AND B)其简化为A OR (A AND B)和一个FULL OUTER JOIN是A OR (A AND B) OR B ，其简化为A OR B 。

Answer 8:

我的方法是学习一般，特别是关系代数关系理论。 然后，学会发现在SQL用来实现从关系代数运算符的构造（例如通用量化又名师）和微积分（如存在量词）。 在疑难杂症的是，SQL在关系模型如空值，这可能是最好的重构远无论如何没有的功能。 推荐阅读： SQL和关系理论：如何编写准确的SQL代码由CJ日期 。

在这方面，我不相信“的事实，大多数子查询可以重写为连接”代表的简化。

就拿这个查询，例如：

SELECT c 
  FROM T1 
 WHERE c NOT IN ( SELECT c FROM T2 );

重写使用JOIN

SELECT DISTINCT T1.c 
  FROM T1 NATURAL LEFT OUTER JOIN T2 
 WHERE T2.c IS NULL;

该联接是更详细的！

可替换地，识别该构建体被实施上的投影的反连接c例如伪algrbra

T1 { c } antijoin T2 { c }

简化使用关系运算符：

SELECT c FROM T1 EXCEPT SELECT c FROM T2;

文章来源: General rules for simplifying SQL statements