SQL EXISTS target and DO NOT EXIST

Question

From time to time, I see them being used, but it never seems like it’s impossible to do just as well, if not better, using a regular join or subquery.

I see them as misleading (they may be more difficult to visualize compared to regular joins and subqueries), often misunderstood (for example, using SELECT * will behave the same as SELECT 1 in the EXISTS/NOT EXISTS subquery), and from my limited experience, slower to perform.

Can someone describe and / or provide me with an example where they are best suited or where there is no other option but to use them? Please note: since their execution and performance are probably platform dependent, I am particularly interested in their use in MySQL .

Answer 1

From time to time, I see them being used, but it never seems like it’s impossible to do just as well, if not better, using a regular join or subquery.

This article (although SQL Server is related):

• IN vs. JOIN or EXISTS

may interest you.

In a nutshell, JOIN is a given operation, and EXISTS is a predicate.

In other words, these queries:

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

against.

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

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

Their colleagues, NOT EXISTS vs. LEFT JOIN / IS NULL are the same in terms of logic, but not in terms of performance.

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

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

Answer 2

if the main query returns much fewer rows, then the table in which 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 connection will be much slower. or a better example if you want to find if an element exists in 1 of several tables.

Answer 3

You cannot [easily] use a join in an UPDATE , so WHERE EXISTS works fine there:

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

Edit: Based on Oracle more than MySQL, and yes, there are ways to do this with the built-in view, but IMHO is cleaner.