存在（选择1…）vs存在（选择*…）一个或另一个？

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
       )

#1 楼

不，在所有主要DBMS中，(NOT) EXISTS (SELECT 1 ...)和(NOT) EXISTS (SELECT * ...)之间的效率没有差异。我经常看到也使用(NOT) EXISTS (SELECT NULL ...)。甚至评估过。


关于(NOT) EXISTS (SELECT 1/0 ...)反连接方法，更正：这等效于LEFT JOIN / IS NULL。

在这种情况下，NOT EXISTS (SELECT ...)与NOT EXISTS可能会得到不同的执行计划。例如，在MySQL中，大多数情况下是在5.7之前的较旧版本中，这些计划将相当相似，但并不完全相同。据我所知，其他DBMS（SQL Server，Oracle，Postgres，DB2）的优化器或多或少地具有重写这两种方法并为两种方法考虑相同计划的能力。尽管如此，仍然没有这样的保证，并且在进行优化时，最好检查来自不同等效重写的计划，因为在某些情况下，每个优化器都不会重写（例如，复杂的查询，具有许多联接和/或派生表/子查询中的子查询，其中多个表的条件，连接条件中使用的组合列的条件）或优化器的选择和计划受可用索引，设置等的影响不同。在所有DBMS（例如SQL Server）中使用。更常见的LEFT JOIN / IS NULL随处可用。
并且在列中需要在USING中使用表名/别名作为前缀，以避免在我们加入时出现错误/含糊不清。在JOIN ... ON检查中（尽管PK或任何不可为空的列都可以，但是当SELECT的计划使用非聚集索引时，这样做可能对效率很有用）：

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 ;

还有另一种用于反联接的方法，使用IS NULL，但是如果内部表的列可为空，则它具有不同的语义（和结果！）。尽管可以通过排除LEFT JOIN的行来使用它，从而使查询等同于先前的两个版本： br />

#2 楼

一类SELECT 1和SELECT *不能互换的情况–更具体地说，在那种情况下总是会被接受，而在另一种情况下却不会。
我说的是需要检查行是否存在的情况。分组的集合。如果表T有C1和C2列，并且您正在检查是否存在匹配特定条件的行组，则可以使用SELECT 1，如下所示： br />这只是一个语法方面。如果在语法上同时接受了这两个选项，则在性能或返回结果方面，您很可能没有任何区别，正如在其他答案中所解释的那样。实际上支持这种区别。 SQL Server，Oracle，MySQL和SQLite等产品将在上面的查询中愉快地接受SELECT *，而不会出现任何错误，这可能意味着它们以特殊方式对待EXISTS SELECT *。
PostgreSQL是一个RDBMS，其中SELECT可能会失败，但可能在某些情况下仍然可以使用。特别是，如果按PK分组，则SELECT *可以正常工作，否则将失败，并显示以下消息：

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

#3 楼

它们相同的“证明”（在MySQL中）是要做的，然后再重复SELECT 1。在这两种情况下，“扩展”输出均显示已将其转换为SELECT 1。类似地，COUNT(*)也被转换为COUNT(0)。

需要注意的另一件事：优化方面的改进最新版本。可能值得比较EXISTS与反联接。您的版本可能比另一版本做得更好。

#4 楼

至少在SQL Server中，重写EXISTS子句可以使查询更整洁，并且可能减少误导的一种有趣的方式可能是：

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

-semi-join版本看起来像：

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

两者通常都被优化为与WHERE EXISTS或WHERE NOT EXISTS相同的计划，但是意图是显而易见的，没有“奇怪的” 1或*。有趣的是，与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;

+--------+-----------+
| ID     | SomeValue |
+--------+-----------+
|  1     | 1         |
|  2     | 2         |
|  3     | 3         |
|  4     | NULL      |
+--------+-----------+

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;

+--------+-----------+
| ID     | SomeValue |
+--------+-----------+
|  1     | 1         |
|  2     | 2         |
|  3     | NULL      |
|  4     | 4         |
+--------+-----------+

NOT IN (...)查询：

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

具有以下计划：



由于空值使相等性无法确认，因此查询不返回任何行。

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

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

使用NOT EXISTS (...)时，显示的平面形状略有不同，并且确实返回行：

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

平面图： />该查询的结果：

+--------+-----------+
| ID     | SomeValue |
+--------+-----------+
|  3     | 3         |
|  4     | NULL      |
+--------+-----------+

这使得使用<> ALL (...)与NOT IN (...)一样容易出现问题。

#5 楼

在某些数据库中，此优化尚不起作用。就像PostgreSQL 9.6版本中的例子一样，这将失败。 br />失败是因为以下操作失败，但这仍然意味着有所不同。我对以下问题的回答：SQL规范是否要求EXISTS（）中使用GROUP BY？

#6 楼

从理论上讲，我一直使用select top 1 'x'（SQL Server）

从理论上讲，select top 1 'x'会比select *效率更高，因为前者将在存在限定行的情况下选择一个常数后完成，而后者会选择一切。

但是，尽管很早就可能有意义，但优化使得差异可能与所有主要RDBS都不相关。

#7 楼

IF EXISTS(SELECT TOP(1) 1 FROM是一个长期习惯，适用于各个平台，因为您甚至不必担心当前平台/版本的优缺点。 SQL正在从TOP n转向可参数化的TOP(n)。这应该是一次学习的技能。