Categories
count oracle sql

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

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.