CODEWITHSUNDEEP
sqlperformance
Houssam Badri
Houssam Badri

Reputation: 2509

COUNT vs SELECT in SQL

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.

Upvotes: 0

Views: 189

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

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).

Upvotes: 2

Adrien Brunelat
Adrien Brunelat

Reputation: 4652

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.

Upvotes: 1

McNets
McNets

Reputation: 10827

Try with IF EXISTS (

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

Upvotes: 0

Related Questions