标识是否至少有一个给定条件的行存在 [英] Identify if at least one row with given condition exists

问题描述

员工表有ID和NAME列。名称可以重复。我想知道是否至少有一行名为kaushik％。

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%'.

所以查询应该返回true / false或1/0。

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%'

在这种情况下，它不返回true / false。
同样我们迭代表中的所有记录。在简单的SQL中有办法，每当第一条满足条件的记录被获取，它应该停止检查更多的记录。
或者这样的事情只能在Pl / SQL块中处理？

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 *
Justin提供的第一种方法看起来正确答案

EDIT * First approach provided by Justin looks correct answer

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

推荐答案

通常，

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

其中 rownum = 1 谓词允许Oracle在找到第一个匹配行或

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%' )

其中 EXISTS 子句允许Oracle在找到第一个匹配行时立即停止查找。

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.

这篇关于标识是否至少有一个给定条件的行存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！