CODEWITHSUNDEEP
sqloracle-databasecount
Kaushik Lele
Kaushik Lele

Reputation: 6637

Identify if at least one row with given condition exists

Employee table has ID and NAME columns. Names can be repeated. I want to find out if there is at least one row with name like 'kaushik%'.

So query should return true/false or 1/0.

Is it possible to find it using single query. If we try something like 

select count(1) from employee where name like 'kaushik%'

in this case it does not return true/false. Also we are iterating over all the records in table. Is there way in simple SQL such that whenever first record which satisfies condition is fetched, it should stop checking further records. Or such thing can only be handled in Pl/SQL block ?

EDIT * First approach provided by Justin looks correct answer 

SELECT COUNT(*) FROM employee WHERE name like 'kaushik%' AND rownum = 1

Upvotes: 35

Views: 124643

Answers (4)

Raghavan
Raghavan

Reputation: 637

select 1 
 where exists ( select name 
                  from employee 
                  where name like 'kaushik%'
               )

Upvotes: 0

Max
Max

Reputation: 4077

since you require that the sql query should return 1 or 0, then you can try the following query :-

select count(1) from dual 
where exists(SELECT 1 
             FROM employee
             WHERE name like 'kaushik%')

Since the above query uses Exists, then it will scan the employee table and as soon as it encounters the first record where name matches "kaushik", it will return 1 (without scanning the rest of the table). If none of the records match, then it will return 0.

Upvotes: 4

Justin Cave
Justin Cave

Reputation: 231661

Commonly, you'd express this as either

SELECT COUNT(*)
  FROM employee
 WHERE name like 'kaushik%'
   AND rownum = 1

where the rownum = 1 predicate allows Oracle to stop looking as soon as it finds the first matching row or

SELECT 1
  FROM dual
 WHERE EXISTS( SELECT 1
                 FROM employee
                WHERE name like 'kaushik%' )

where the EXISTS clause allows Oracle to stop looking as soon as it finds the first matching row.

The first approach is a bit more compact but, to my eye, the second approach is a bit more clear since you really are looking to determine whether a particular row exists rather than trying to count something. But the first approach is pretty easy to understand as well.

Upvotes: 42

Gordon Linoff
Gordon Linoff

Reputation: 1269763

How about:

select max(case when name like 'kraushik%' then 1 else 0 end)
from employee

Or, what might be more efficient since like can use indexes:

select count(x)
from (select 1 as x
      from employee
      where name like 'kraushik%'
     ) t
where rownum = 1

Upvotes: 9

Related Questions