36

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?

45

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.

  • 1
    Until very recent versions of MySQL, [NOT] IN (SELECT ...), although equivalent, performed very poorly. Avoid it! – Rick James Dec 29 '16 at 17:39
  • 3
    This is not true for PostgreSQL. SELECT * is certainly doing more work. I would for simplicity sake advise using SELECT 1 – Evan Carroll Mar 24 '17 at 2:34
11

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

  • 1
    Good points, although this isn't exactly the case I was concerned about. This one shows a conceptual difference. Because, when you GROUP BY, the concept of * is meaningless (or, at least, not that clear). – joanolo Dec 29 '16 at 20:23
5

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:

enter image description here

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

enter image description here

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:

enter image description here

The results of that query:

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

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

  • 3
    I must say I don't find * to be strange: I read EXISTS (SELECT * FROM t WHERE ...) AS there is a _row_ in table _t_ that.... Anyhow, I like to have alternatives, and yours is clearly readable. One doubt/caveat: how will it behave if b is nullable? [I've had bad experiences and some short nights when trying to find out a misstake caused by a x IN (SELECT something_nullable FROM a_table)] – joanolo Dec 29 '16 at 21:37
  • EXISTS tells you whether a table has a row & returns true or false. EXISTS (SELECT x FROM (values (null)) is true. IN is =ANY & NOT IN is <>ALL. These 4 take a RHS row with NULLs to possibly match. (x) =ANY (values (null)) & (x) <>ALL (values (null)) are unknown/null but EXISTS (values (null)) is true. (IN & =ANY have the same "null check problems associated with NOT IN (...) [&] <>ALL (...)". ANY & ALL iterate OR & AND. But there are only "problems" if you don't organize the semantics as intended.) Don't advise using these for EXISTS. They are misleading, not "less misleading". – philipxy Jun 3 at 21:46
  • @philliprxy - If I'm wrong, I have no problem admitting it. Feel free to add your own answer if you feel like it. – Max Vernon Jun 3 at 22:09
4

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.

4

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

  • A rare corner case, a little weird maybe, but yet again, a prove that you have to make lots of compromises when designing a database... – joanolo Mar 24 '17 at 7:00
-1

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.

  • Makes sense. That might be (or might have been) one of the very few cases where top n without order by are a good idea. – joanolo Dec 31 '16 at 13:22
  • 3
    "Theoretically, ...." No, theoretically select top 1 'x' should not be more efficient than select *in an Existexpression. Practically it may be more efficient if the optimizer is working suboptimal but theoretically both expressions are equivalent. – miracle173 Jan 7 '17 at 15:52
-4

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.

  • 3
    What do you mean with "across platforms"? TOP is not even valid SQL. – ypercubeᵀᴹ Mar 22 '18 at 21:17
  • "SQL is moving.." is plain wrong. There is no TOP (n) in "SQL" - the standard query language. There is one on T-SQL which is the dialect Microsoft SQL Server is using. – a_horse_with_no_name Aug 28 '18 at 20:52
  • The tag on the original question is "SQL Server". But it is okay to downvote and dispute what I said - it's the purpose of this site to enable easy downvoting. Who am I to rain on your parade with boring attention to detail? – ajeh Aug 29 '18 at 22:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.