主页 / dba / 问题 / 159413
Accepted
joanolo
Asked: 2016-12-30 00:02:32 +0800 CST

EXISTS (SELECT 1 ...) vs EXISTS (SELECT * ...) 一个还是另一个？

  • 772

每当我需要检查表中是否存在某行时，我倾向于总是写一个条件，例如：

SELECT a, b, c
  FROM a_table
 WHERE EXISTS
       (SELECT *  -- This is what I normally write
          FROM another_table
         WHERE another_table.b = a_table.b
       )

其他一些人这样写：

SELECT a, b, c
  FROM a_table
 WHERE EXISTS
       (SELECT 1   --- This nice '1' is what I have seen other people use
          FROM another_table
         WHERE another_table.b = a_table.b
       )

当条件NOT EXISTS不是EXISTS: 在某些情况下，我可能会用 aLEFT JOIN和一个额外的条件（有时称为antijoin）来编写它：

SELECT a, b, c
  FROM a_table
       LEFT JOIN another_table ON another_table.b = a_table.b
 WHERE another_table.primary_key IS NULL

我尽量避免它，因为我认为含义不太清楚，特别是当您primary_key的内容不是那么明显时，或者当您的主键或连接条件是多列时（并且您很容易忘记其中一列）。但是，有时您维护由其他人编写的代码……它就在那里。

  1. 有什么区别（除了风格）来SELECT 1代替SELECT *吗？
    有没有表现不同的极端情况？

  2. 虽然我写的是（AFAIK）标准SQL：不同的数据库/旧版本有这样的区别吗？

  3. 明确写反加入有什么好处吗？
    当代计划者/优化者是否将其与NOT EXISTS条款区别对待？

sql-server mysql

7 个回答

  1. Best Answer

    (NOT) EXISTS (SELECT 1 ...)不，(NOT) EXISTS (SELECT * ...)所有主要 DBMS之间的效率没有差异。我也经常看到(NOT) EXISTS (SELECT NULL ...)被使用。

    在某些情况下，您甚至可以编写(NOT) EXISTS (SELECT 1/0 ...)并且结果是相同的 - 没有任何（除以零）错误，这证明那里的表达式甚至没有被评估。

    关于LEFT JOIN / IS NULLantijoin 方法，更正一下：这相当于NOT EXISTS (SELECT ...).

    在这种情况下，NOT EXISTSvsLEFT JOIN / IS NULL，你可能会得到不同的执行计划。例如，在 MySQL 中，主要是在旧版本（5.7 之前）中，计划非常相似，但并不完全相同。据我所知，其他 DBMS（SQL Server、Oracle、Postgres、DB2）的优化器或多或少能够重写这两种方法并为两者考虑相同的计划。尽管如此，仍然没有这样的保证，并且在进行优化时，最好检查来自不同等效重写的计划，因为可能存在每个优化器不重写的情况（例如，复杂查询，具有许多连接和/或派生表/子查询中的子查询，其中来自多个表的条件、连接条件中使用的复合列）或优化器选择和计划受可用索引、设置等的不同影响。

    另请注意，USING不能在所有 DBMS 中使用（例如 SQL Server）。比较常见的JOIN ... ON作品无处不在。
    并且列需要以表名/别名为前缀，SELECT以避免在我们进行连接时出现错误/歧义。
    我通常也更喜欢将连接的列放在IS NULL检查中（尽管 PK 或任何不可为空的列都可以，但当计划LEFT JOIN使用非聚集索引时，它可能对效率很有用）：

    SELECT a_table.a, a_table.b, a_table.c
  FROM a_table
       LEFT JOIN another_table 
           ON another_table.b = a_table.b
 WHERE another_table.b IS NULL ;

    还有第三种反连接方法，NOT IN但是如果内部表的列可以为空，则使用它具有不同的语义（和结果！）。它可以通过排除带有 的行来使用NULL，使查询等效于前两个版本：

    SELECT a, b, c
  FROM a_table
 WHERE a_table.b NOT IN 
       (SELECT another_table.b
          FROM another_table
         WHERE another_table.b IS NOT NULL
       ) ;

    这通常也会在大多数 DBMS 中产生类似的计划。

    • 57

  2. 有一类情况是不可互换的——更具体地说，在这些情况下总是会接受一种情况，SELECT 1而SELECT *另一种情况大多不会。

    我说的是您需要检查分组集的行是否存在的情况。如果表T有列C1，C2并且您正在检查是否存在与特定条件匹配的行组，则可以SELECT 1这样使用：

    EXISTS
(
  SELECT
    1
  FROM
    T
  GROUP BY
    C1
  HAVING
    AGG(C2) = SomeValue
)

    但你不能SELECT *以同样的方式使用。

    这只是一个句法方面。在语法上接受这两个选项的情况下，您很可能在性能或返回的结果方面没有差异，正如另一个答案中所解释的那样。

    评论后的附加说明

    似乎没有多少数据库产品实际上支持这种区别。SQL Server、Oracle、MySQL 和 SQLite 等产品很乐意接受SELECT *上述查询而不会出现任何错误，这可能意味着它们SELECT以特殊方式处理 EXISTS。

    PostgreSQL 是一种SELECT *可能会失败的 RDBMS，但在某些情况下仍然可以工作。特别是，如果您按 PK 分组，SELECT *则可以正常工作，否则将失败并显示以下消息：

    错误：列“T.C2”必须出现在 GROUP BY 子句中或在聚合函数中使用

    • 13

  3. 它们相同的“证明”（在 MySQL 中）是

    EXPLAIN EXTENDED
    SELECT EXISTS ( SELECT * ... ) AS x;
SHOW WARNINGS;

    然后用 重复SELECT 1。在这两种情况下，“扩展”输出都表明它已转换为SELECT 1.

    同样，COUNT(*)变成COUNT(0).

    另一件需要注意的事情：在最近的版本中进行了优化改进。可能值得比较EXISTS与反连接。您的版本可能会比其他版本做得更好。

    • 5

  4. 至少在 SQL Server 中，重写EXISTS子句的一种可能很有趣的方式是：

    SELECT a, b, c
  FROM a_table
 WHERE b = ANY
       (
          SELECT b
          FROM another_table
       );

    它的反半连接版本如下所示：

    SELECT a, b, c
  FROM a_table
 WHERE b <> ALL
       (
          SELECT b
          FROM another_table
       );

    两者通常都针对与WHERE EXISTSor相同的计划进行优化WHERE NOT EXISTS，但其意图是明确无误的，并且您没有“奇怪”的1or *。

    有趣的是，与 相关的空值检查问题对于来说NOT IN (...)是有问题的<> ALL (...)，而NOT EXISTS (...)不存在这个问题。考虑以下两个具有可为空列的表：

    IF OBJECT_ID('tempdb..#t') IS NOT NULL
BEGIN
    DROP TABLE #t;
END;
CREATE TABLE #t 
(
    ID INT NOT NULL IDENTITY(1,1)
    , SomeValue INT NULL
);

IF OBJECT_ID('tempdb..#s') IS NOT NULL
BEGIN
    DROP TABLE #s;
END;
CREATE TABLE #s 
(
    ID INT NOT NULL IDENTITY(1,1)
    , SomeValue INT NULL
);

    我们将向两者添加一些数据，其中一些匹配的行，一些不匹配的行：

    INSERT INTO #t (SomeValue) VALUES (1);
INSERT INTO #t (SomeValue) VALUES (2);
INSERT INTO #t (SomeValue) VALUES (3);
INSERT INTO #t (SomeValue) VALUES (NULL);

SELECT *
FROM #t;

    +--------+------------+
| 身份证 | 一些价值 |
+--------+------------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 空 |
+--------+------------+
    INSERT INTO #s (SomeValue) VALUES (1);
INSERT INTO #s (SomeValue) VALUES (2);
INSERT INTO #s (SomeValue) VALUES (NULL);
INSERT INTO #s (SomeValue) VALUES (4);

SELECT *
FROM #s;

    +--------+------------+
| 身份证 | 一些价值 |
+--------+------------+
| 1 | 1 |
| 2 | 2 |
| 3 | 空 |
| 4 | 4 |
+--------+------------+

    NOT IN (...)查询：

    SELECT *
FROM #t 
WHERE #t.SomeValue NOT IN (
    SELECT #s.SomeValue
    FROM #s 
    );

    有以下计划：

    在此处输入图像描述

    该查询不返回任何行，因为 NULL 值使相等性无法确认。

    此查询<> ALL (...)显示相同的计划并且不返回任何行：

    SELECT *
FROM #t 
WHERE #t.SomeValue <> ALL (
    SELECT #s.SomeValue
    FROM #s 
    );

    在此处输入图像描述

    using 的变体NOT EXISTS (...)显示了稍微不同的计划形状，并且确实返回了行：

    SELECT *
FROM #t 
WHERE NOT EXISTS (
    SELECT 1
    FROM #s 
    WHERE #s.SomeValue = #t.SomeValue
    );

    计划：

    在此处输入图像描述

    该查询的结果：

    +--------+------------+
| 身份证 | 一些价值 |
+--------+------------+
| 3 | 3 |
| 4 | 空 |
+--------+------------+

    这使得使用<> ALL (...)就像NOT IN (...).

    • 5

  5. 在某些数据库中，这种优化还不起作用。就像在 PostgreSQL中一样，从 9.6 版开始，这将失败。

    SELECT *
FROM ( VALUES (1) ) AS g(x)
WHERE EXISTS (
  SELECT *
  FROM ( VALUES (1),(1) )
    AS t(x)
  WHERE g.x = t.x
  HAVING count(*) > 1
);

    这将成功。

    SELECT *
FROM ( VALUES (1) ) AS g(x)
WHERE EXISTS (
  SELECT 1  -- This changed from the first query
  FROM ( VALUES (1),(1) )
    AS t(x)
  WHERE g.x = t.x
  HAVING count(*) > 1
);

    它失败了，因为以下失败了，但这仍然意味着存在差异。

    SELECT *
FROM ( VALUES (1),(1) ) AS t(x)
HAVING count(*) > 1;

    您可以在我对问题的回答中找到有关此特定怪癖和违反规范的更多信息，SQL 规范是否需要 EXISTS () 中的 GROUP BY

    • 5

  6. 我一直使用select top 1 'x'（SQL Server）

    从理论上讲，select top 1 'x'这会更有效select *，因为前者在选择存在合格行的常数后将是完整的，而后者将选择所有内容。

    然而，尽管在很早的时候它可能是相关的，但优化已经使可能所有主要的 RDBS 中的差异无关紧要。

    • -1

  7. IF EXISTS(SELECT TOP(1) 1 FROM长期和跨平台是一个更好的习惯，因为您甚至不需要开始担心当前平台/版本的好坏；并且 SQL 正在从TOP n参数化转向TOP(n)。这应该是一次学习的技能。

    • -5

相关问题