Often, to do an existence check for data, people issue a “SELECT COUNT(…)” against the relevant table. So in pseudo-code, the logic looks like something like this:
select count(*)
into my_variable
from MY_TABLE
where COL =
if my_variable > 0 then ...
For unique key lookups, then that’s fine, but it’s a risky strategy as a general premise, because counting all of the records can be costly, especially if you’re only interested in the first one. You are coding always hoping that some index lookup is going to be possible. And in strict terms, you actually are not meeting the requirement. Someone asked you to check for existence, NOT to get an exact count of how many matches there were. You are doing more work than you need to.
So here are some potentially better alternatives:
Option 1: stop after the first row
This is a quick and easy win, because you are simply adding a predicate to the existing code.
select count(*)
into my_variable
from MY_TABLE
where COL =
and rownum = 1
if my_variable > 0 then ...
Option 2: use an existence check
This is often referred to as making the SQL “self-documenting”, because the code is describing the kind of check you were doing, that is, you were interested in existence, not the count
select count(*)
into my_variable
from dual
where exists (
select *
from MY_TABLE
where COL = )
if my_variable > 0 then ..
The big caveat…
Perhaps the best way to check if something exists is sometimes just not to check at all ! Because lets face it, if your code looks something like:
<check for existence>
if <not exists> then
insert into …
then you really haven’t achieved much. The check for existence does nothing to ensure that by the time you execute the INSERT, the row doesn’t now exist, so your code still has to handle the case where you tried to insert a row and found it already there.
And if you have to do that…then did you really get any benefit in running the <check for existence> code in the first place ?
Learning is not a spectator sport 
Leave a reply to exagriddba Cancel reply