Normally, to check existence in Oracle I will do:
SELECT COUNT(1) FROM foo WHERE bar = 'baz'
However, if the
foo table contains multiple rows where
bar='baz', this query needlessly scans through the entire table in order to report the total count.
How do I quickly check if a column in a table contains at least one row with a specified value, and have the query short-circuit, such that as soon as it detects that the value exists, the query will stop and return, as opposed to scanning the entire table?
Ideally I would want this query to return a scalar value, regardless of how many rows in the table contain the value.
The following solution appears to short-circuit (compared to
SELECT COUNT(1), this is much faster for a large table), however it will either return 1 if the value exists, or return no rows at all if the value does not exist. It would be better if a row was always returned.
SELECT 1 FROM DUAL WHERE EXISTS ( SELECT 1 FROM foo WHERE bar = 'baz' )