The purpose of SQL's EXISTS and NOT EXISTS

Question

Every now and then I see these being used, but it never seems to be anything that can't be performed as equally well, if not better, by using a normal join or subquery.

I see them as being misleading (they're arguably harder to accurately visualize compared to conventional joins and subqueries), often misunderstood (e.g. using SELECT * will behave the same as SELECT 1 in the EXISTS/NOT EXISTS subquery), and from my limited experience, slower to execute.

Can someone describe and/or provide me an example where they are best suited or where there is no option other than to use them? Note that since their execution and performance are likely platform dependent, I'm particularly interested in their use in MySQL.

I found this link that compares `exists` to `in`. It's for MS-SQL Server but the same principle should apply: http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx ...Another link, this one more performance-related: http://oracle-online-help.blogspot.com/2006/11/in-vs-exist-in-sql.html Another one... http://www.java2s.com/Tutorial/Oracle/0040__Query-Select/EXISTSandNOTEXISTSVersusINandNOTIN.htm — FrustratedWithFormsDesigner, Dec 16 '10 at 21:44
Go figure. I find using EXISTS and NOT EXISTS much clearer than creating an unneeded JOIN when I'm interested in rows from only a single table. — Larry Lustig, Dec 20 '10 at 19:33
+1 Larry. Indeed. And JOIN have some side effects: http://stackoverflow.com/questions/1474964/using-tuples-in-sql-in-clause/1474974#1474974 — Michael Buen, Dec 22 '10 at 05:02

score 5 · Accepted Answer · answered Dec 20 '10 at 17:59

Every now and then I see these being used, but it never seems to be anything that can't be performed as equally well, if not better, by using a normal join or subquery.

This article (though SQL Server related):

IN vs. JOIN vs. EXISTS

may be of interest to you.

In a nutshell, JOIN is a set operation, while EXISTS is a predicate.

In other words, these queries:

SELECT  *
FROM    a
JOIN    b
ON      some_condition(a, b)

vs.

SELECT  *
FROM    a
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    b
        WHERE   some_condition(a, b)
        )

are not the same: the former can return more than one record from a, while the latter cannot.

Their counterparts, NOT EXISTS vs. LEFT JOIN / IS NULL, are the same logically but not performance-wise.

In fact, the former may be more efficient in SQL Server:

NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server

score 1 · Answer 2 · answered Dec 16 '10 at 21:43

1

if the main query returned much less rows then the table where you want to find them. example:

SELECT st.State
FROM states st
WHERE st.State LIKE 'N%' AND EXISTS(SELECT 1 FROM addresses a WHERE a.State = st.State)

doing this with a join will be much slower. or a better example, if you want to search if a item exists in 1 of multiple tables.

answered Dec 16 '10 at 21:43

The Scrum Meister

29,681
8
66
64

If you insisted on forcing *join* order, I'd argue that using a STRAIGHT JOIN would be as efficient, if not more. For your example, something like `SELECT st.State FROM states st STRAIGHT_JOIN addresses a ON a.State = st.State WHERE st.State LIKE 'N%'` – Riedsio Dec 17 '10 at 16:13
@Riedsio i don't want a JOIN. say the addresses table has many records for every state. – The Scrum Meister Dec 17 '10 at 18:52
I think I getcha now. I can see how'd you get some improvements (or at least couldn't hurt) to put a LIMIT 1 in the EXISTS clause -- i.e. ... AND EXISTS(SELECT 1 FROM addresses a WHERE a.State = st.State LIMIT 1) – Riedsio Dec 21 '10 at 20:51

BQ. · Answer 3 · 2010-12-16T23:49:21.073

1

You can't [easily] use a join in an UPDATE statement, so WHERE EXISTS works excellently there:

UPDATE mytable t
   SET columnX = 'SomeValue'
 WHERE EXISTS 
   (SELECT 1 
      FROM myothertable ot
     WHERE ot.columnA = t.columnY
       AND ot.columnB = 'XYX'
   );

Edit: Basing this on Oracle more than MySQL, and yes there are ways to do it with an inline view, but IMHO this is cleaner.

edited Dec 16 '10 at 23:49

answered Dec 16 '10 at 23:38

BQ.

9,393
3
25
35

BQ: In MySQL, I'm pretty sure you can write that as `UPDATE mytable t JOIN myothertable ot ON ot.columnA = t.columnY SET columnX = 'SomeValue' WHERE ot.columnB = 'XYX'` – Gabe Dec 17 '10 at 00:27
That's an interesting observation, but it's MySQL I'm particularly concerned with. – Riedsio Dec 17 '10 at 16:02

The purpose of SQL's EXISTS and NOT EXISTS

3 Answers3