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

一尘不染

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

sql-server

每当我需要检查表中是否存在某行时，我倾向于始终编写如下条件：

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的内容不那么明显时，或者当您的主键或连接条件是多列时（您很容易忘记其中一列）。但是，有时您维护由其他人编写的代码......它就在那里。

有什么区别（除了风格）来SELECT 1代替SELECT *吗？
有没有表现不同的极端情况？
虽然我写的是（AFAIK）标准SQL：不同的数据库/旧版本有这样的区别吗？
明确写反加入有什么好处吗？
当代计划者/优化者是否将其与NOT EXISTS条款区别对待？

阅读 198

2022-10-31

共1个答案

一尘不染

(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 中产生类似的计划。

2022-10-31