COUNT vs SELECT in SQL

Question

What is better approach to check existence of an object in database?

select count(id) as count from my_table where name="searchedName";

OR

select id from my_table where name="searchedName";

And then check if count > 0 or the object is not null (ORM logic)

EDIT:

select id to be valid for Oracle.

Which dbms are you using? Those queries are product specific. — jarlh
– jarlh, Commented Apr 10, 2018 at 9:10
You develope in H2, but production is Oracle? That's a very bad idea. Not every query that works in H2 works in Oracle too. (E.g. the standard SQL query 'select 1;' is invalid in Oracle.) Your queries are both invalid by the way; string delimiter is the single quote. — Thorsten Kettner
– Thorsten Kettner, Commented Apr 10, 2018 at 10:11

Thorsten Kettner · Accepted Answer · 2018-04-10 09:56:26Z

2

The idea should be to that we only need to find one record in order to say that such record exists. This can be done with an EXISTS clause in standard SQL.

select exists (select * from mytable where name = 'searchedName');

returns true if the table contains a record with 'searchedName' and false otherwise.

If you want 0 for false and 1 for true instead (e.g. if the DBMS does not support booleans):

select case when exists (select * from mytable where name = 'searchedName') 
  then 1 else 0 end as does_exist;

You say you want this for Oracle. In Oracle you can use above query, but you'd have to select from the table dual:

select case when exists (select * from mytable where name = 'searchedName') 
  then 1 else 0 end as does_exist
from dual;

But for Oracle we'd usually use rownum instead:

select count(*) as does_exist
from mytable 
where name = 'searchedName'
and rownum = 1; -- to find one record suffices and we'd stop then

This also returns 1 if the table contains a record with 'searchedName' and 0 otherwise. This is a very typical way in Oracle to limit lookups and the query is very readable (in my opinion).

edited Apr 10, 2018 at 9:56

answered Apr 10, 2018 at 9:51

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

Although I like the answer, I still don't like the Oracle solution. Using count(*0 for this purpose just sends the wrong signal -- even with the rownum = 1.

Adrien Brunelat · Accepted Answer · 2018-04-10 09:11:30Z

1

I'd just call:

select id from my_table where name='searchedName';

Making sure there is an index for the name column. And then check whether or not the result is empty.

answered Apr 10, 2018 at 9:11

Adrien Brunelat

4,6704 gold badges35 silver badges44 bronze badges

2 Comments

Adrien Brunelat Over a year ago

If you need to use the result logic for another SQL query, I'd go back to the back-end language (for instance Java or PHP) and then depending on the result, call another SQL query.

Adrien Brunelat Over a year ago

But if that's the case, you should also post that part of the SQL logic for us to optimize our answers (both queries could be combined)

McNets · Accepted Answer · 2018-04-10 09:10:05Z

0

Try with IF EXISTS (

if exists (select 1 from my_table where name = "searchedName")
begin
     ....
end

answered Apr 10, 2018 at 9:10

McNets

10.9k3 gold badges39 silver badges70 bronze badges

Collectives™ on Stack Overflow

COUNT vs SELECT in SQL

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related