SELECT EXISTS vs. LIMIT 1

--------------------------------------------------
Rise to the top 3% as a developer or hire one of them at Toptal: https://topt.al/25cXVn
--------------------------------------------------

Music by Eric Matyas
https://www.soundimage.org
Track title: Over Ancient Waters Looping

--

Chapters
00:00 Select Exists Vs. Limit 1
00:46 Answer 1 Score 9
00:58 Accepted Answer Score 5
01:26 Answer 3 Score 3
01:43 Thank you

--

Full question
https://stackoverflow.com/questions/1059...

--

Content licensed under CC BY-SA
https://meta.stackexchange.com/help/lice...

--

Tags
#python #sql #postgresql

#avk47

ANSWER 1

Score 9

To my eye the second statement is problematic in that it will not return a row if the condition is not satisfied.

ACCEPTED ANSWER

Score 5

PostgreSQL seems to clever enough to treat both statements alike as you can clearly see in your execution plans.

My tests with a local table with ~150000 rows and ~100 selected out of that by the condition also show the same behaviour

The bottomline is: it doesn't really matter which one you use, but you should be aware that other DBMS might not behave in the same way.

ANSWER 3

Score 3

When you use EXPLAIN you can see that the first statement will execute an additional subquery and the second does not.

That is why I would prefer using limit instead of exists

Example:

explain SELECT EXISTS (SELECT 1 FROM checkout WHERE id = 3);
explain SELECT 1 FROM checkout WHERE id = 3 limit 1;