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 

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

Answer 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