Because of using Hibernate, I cannot write a JPA query using exists like this (I translated it to pure SQL query):
SELECT EXISTS (SELECT * FROM account WHERE activated = true)
So I have to write an alternative query to check existance of activated accounts. After reading several suggestions on this website, I see a replacement:
select case when count(*)> 0 then true else false end from account where activated = true
My question is: Is this bad for performance, is it short circuit query. Means whether the query stops executing if it finds at least one activated account? Or it has to get all activated account then at the final, check the total result if greater than zero or not?
Advertisement
Answer
Your query seems reaching, why not just a simple
select count(*)
from account
where activated = true
Done, you get one record back with a count. But if you are looking just to see if ANY account is activated, all you need is
select 1
from account
where activated = true
limit 1
Here, you are forcing a limit of 1, so as soon as the first qualifying is found, the query is done, and you can proceed — even if your database has 1000’s (or even millions) of records.