EXISTS (SELECT 1 ...) vs EXISTS (SELECT * ...) One or the other?

Question

Whenever I need to check for the existence of some row in a table, I tend to write always a condition like:

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
       )

Some other people write it like:

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
       )

When the condition is NOT EXISTS instead of EXISTS: In some occasions, I might write it with a LEFT JOIN and an extra condition (sometimes called an 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

I try to avoid it because I think the meaning is less clear, specially when what is your primary_key is not that obvious, or when your primary key or your join condition is multi-column (and you can easily forget one of the columns). However, sometimes you maintain code written by somebody else... and it is just there.

1. Is there any difference (other than style) to use SELECT 1 instead of SELECT *?
   Is there any corner case where it does not behave the same way?

2. Although what I wrote is (AFAIK) standard SQL: Is there such a difference for different databases / older versions?

3. Is there any advantage on explicity writing an antijoin?
   Do contemporary planners/optimizers treat it differently from the NOT EXISTS clause?

Answer 1

No, there is no difference in efficiency between (NOT) EXISTS (SELECT 1 ...) and (NOT) EXISTS (SELECT * ...) in all major DBMS. I've often seen (NOT) EXISTS (SELECT NULL ...) being used as well.

In some you can even write (NOT) EXISTS (SELECT 1/0 ...) and the result is the same - without any (division by zero) error, which proves that the expression there is not even evaluated.

---

About the LEFT JOIN / IS NULL antijoin method, a correction: this is equivalent to NOT EXISTS (SELECT ...).

In this case, NOT EXISTS vs LEFT JOIN / IS NULL, you may get different execution plans. In MySQL for example and mostly in older versions (before 5.7) the plans would be fairly similar but not identical. The optimizers of other DBMS (SQL Server, Oracle, Postgres, DB2) are - as far as I know - more or less capable of rewriting these 2 methods and considering the same plans for both. Still, there is no such guarantee and when doing optimization, it is good to check the plans from different equivalent rewrites as there could be cases that each optimizer doesn't rewrite (eg. complex queries, with many joins and/or derived tables / subqueries inside the subquery, where conditions from multiple tables, composite columns used in the joining conditions) or the optimizer choices and plans are affected differently by the available indexes, settings, etc.

Also note that USING cannot be used in all DBMS (SQL Server for example). The more common JOIN ... ON works everywhere.
And the columns needs to be prefixed with the table name/alias in the SELECT to avoid errors/ambiguities when we have joins.
I also usually prefer to put the joined column in the IS NULL check (although the PK or any non-nullable column would be OK, it might be useful for efficiency when the plan for LEFT JOIN uses a non-clustered index):

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 ;

There is also a third method for antijoins, using NOT IN but this has different semantics (and results!) if the column of the inside table is nullable. It can be used though by excluding the rows with NULL, making the query equivalent to the previous 2 versions:

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
       ) ;

This also usually yields similar plans in most DBMS.

Answer 2

There is one category of cases where SELECT 1 and SELECT * are not interchangeable – more specifically, one will always be accepted in those cases while the other mostly will not.

I am talking about cases where you need to check for existence of rows of a grouped set. If table T has columns C1 and C2 and you are checking for existence of row groups that match a specific condition, you can use SELECT 1 like this:

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

but you cannot use SELECT * in the same way.

That is merely a syntactic aspect. Where both options are accepted syntactically, you will most likely have no difference in terms of performance or the results returned, as has been explained in the other answer.

Additional notes following comments

It appears not many database products actually support this distinction. Products like SQL Server, Oracle, MySQL and SQLite will happily accept SELECT * in the above query without any errors, which probably means they treat an EXISTS SELECT in a special way.

PostgreSQL is one RDBMS where SELECT * may fail, but may still work in some cases. In particular, if you are grouping by the PK, SELECT * will work fine, otherwise it will fail with the message:

ERROR: column "T.C2" must appear in the GROUP BY clause or be used in an aggregate function

Answer 3

The "proof" that they are identical (in MySQL) is to do

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

then repeat with SELECT 1. In both cases, the 'extended' output shows that it was transformed into SELECT 1.

Similarly, COUNT(*) is turned into COUNT(0).

Another thing to note: Optimization improvements have been made in recent versions. It may be worth comparing EXISTS vs anti-joins. Your version may do a better job with one versus the other.

Answer 4

An arguably interesting way of re-writing the EXISTS clause that results in a cleaner, and perhaps less misleading query, at least in SQL Server would be:

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

The anti-semi-join version of that would look like:

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

Both are typically optimized to the same plan as WHERE EXISTS or WHERE NOT EXISTS, but the intention is unmistakable, and you have no "strange" 1 or *.

Interestingly, the null check problems associated with NOT IN (...) are problematic for <> ALL (...), whereas the NOT EXISTS (...) does not suffer from that problem. Consider the following two tables with a nullable column:

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
);

We'll add some data to both, with some rows that match, and some that don't:

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         |
+--------+-----------+

The NOT IN (...) query:

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

Has the following plan:

The query returns no rows since the NULL values make equality impossible to confirm.

This query, with <> ALL (...) shows the same plan and returns no rows:

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

The variant using NOT EXISTS (...), shows a slightly different plan shape, and does return rows:

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

The plan:

The results of that query:

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

This makes using <> ALL (...) just as prone to problematic results as NOT IN (...).

Answer 5

In some databases this optimization doesn't yet work. Like for instance in PostgreSQL As of version 9.6, this will fail.

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
);

And this will succeed.

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
);

It's failing because the following fails but that still means there is a difference.

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

You can find more information about this particular quirk and violation of the spec in my answer to the question, Does the SQL Spec require a GROUP BY in EXISTS ()

Answer 6

I have always used select top 1 'x' (SQL Server)

Theoretically, select top 1 'x' would be more efficient that select *, as the former would be complete after selecting a constant on the existence of a qualifying row, whereas the latter would select everything.

HOWEVER, although very early on it may have been relevant, optimization has made the difference irrelevant in probably all major RDBS's.

Answer 7

IF EXISTS(SELECT TOP(1) 1 FROM is a better habit long term and across platforms simply because you do not need to even start worrying about how good or bad your current platform/version is; and SQL is moving from TOP n towards parameterizable TOP(n). This should be a learn-once skill.