Sometimes in your PL/SQL code you need to query a table which may or may not contain what you’re looking for. This post will cover some different ways of doing it.
Suppose you need to find an employee’s ID named
SAM. If he doesn’t exist in the table then insert a new employee record.
Side note: Technically you can use a
merge statement for this functionality but want to highlight that a row may not be found.
An error is raised if the employee (
SAM in this case) isn’t found. Here are some options to get around this.
This is the most correct way to know if the value exists or not. Some don’t like to use this approach as it can clutter code (due to additional lines of code), on the positive side it is very explicit.
Using an aggregate function will always return a value even if no rows are returned. For example:
select nvl(max(dummy), 'still returns value') val
Using this logic with the base example we can do:
I’ve used this in the past, however it comes with a caveat. If used on a column that isn’t unique it will still return one value. If multiple values are present (ex: all
MANAGER) it can lead to false positives.
Similar to the previous option, selecting from
dual (always returns a row) we can
left join to optionally find the value.
I prefer this approach compared to the
max approach since if multiple values are possible an error is returned (which in most cases we want):