SELECT count(*)
INTO v_any_wahlbergs
FROM actor a
JOIN film_actor fa USING (actor_id)
WHERE a.last_name = 'WAHLBERG'

… where after they discard the exact count to only check for existence:

IF v_any_wahlbergs = 0 THEN
something();
ELSE
something_else();
END IF;

It doesn’t matter if the client logic is written in PL/SQL (as above), or in any other language like Java, the overhead is significant compared to the following, much simpler EXISTS() query:

SELECT CASE WHEN EXISTS (
SELECT * FROM actor a
JOIN film_actor fa USING (actor_id)
WHERE a.last_name = 'WAHLBERG'
) THEN 1 ELSE 0 END
INTO v_any_wahlbergs
FROM dual

Clearly, you can see the effect is the same, and the database can optimize the existence check by taking a shortcut once it has found at least one result (instead of going through the entire result set to get the exact count, which wasn’t needed in the first place).

This Is True Also for Primary Key Checks

The above is obvious. But then, during a recent execution of my SQL Masterclass training, one of the delegates asked me a very interesting question.

Is this also true for primary key checks?

Now, I personally always prefer the EXISTS() syntax because it clearly communicates that I’m after an existence check, not an actual count query. But in principle, the following two queries are exactly the same:

-- Can be 0 or 1
SELECT count(*) FROM film WHERE film_id = 1;
-- Can also be 0 or 1
SELECT CASE WHEN EXISTS (
SELECT * FROM film WHERE film_id = 1
) THEN 1 ELSE 0 END
FROM dual;