Identify if at least one row with given condition exists

Question

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

Solution 1

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.

Solution 2

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

Solution 3

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.