Fastest way to check if row exist

Question

Before a select SOMETHING into v_something, I would like to know if my query returns a row.

Here are a good methods, but it cost two select if the row exists :

select count(1) into isRowExists from PERSON where CONDITION='Something';

if (isRowExists > 0) then
    select NAME into v_name from PERSON where CONDITION='Something';
else
    raise name_not_found;
end if;

select count(1) into isRowExists from CAR where CONDITION='Something';

if (isRowExists > 0) then
    select MODEL into v_model from CAR where CONDITION='Something';
else
    raise model_not_found;
end if;

Or something like that :

select NAME into v_name from PERSON where CONDITION='Something';

select MODEL into v_model from CAR where CONDITION='Something';

exception
    when no_data_found then
        --do_something

But with this method, I don't know if the problem came from PERSON or CAR...

Is there any other solution ? Like sending a parameter to an exception ?

Answer 1

You could do:

BEGIN
    BEGIN
        select NAME into v_name from PERSON where CONDITION='Something';
    exception
        when no_data_found then
        --do_something
    END;

    BEGIN
        select MODEL into v_model from CAR where CONDITION='Something';
    exception
        when no_data_found then
            --do_something
    END;
END;
/

Answer 2

For the second method, you can just wrap each in a begin/end block:

begin
    select NAME into v_name from PERSON where CONDITION='Something';
exception
    when no_data_found then
        --do_something
end;

begin
    select MODEL into v_model from CAR where CONDITION='Something';

exception
    when no_data_found then
        --do_something
end;

Answer 3

if name is not null you can try this:

select (select NAME from PERSON where CONDITION='Something') into v_name 
from dual;

if v_name is null then 
...

I wouldn't say that catching NO_DATA_FOUND is the fastest way. It really depends. Sometimes it's better to perform count and then fetch. COUNT works faster then retrieving data so if there is a high probability that a row doesn't exist COUNT will be more beneficial. And Oracle uses some caching mechanisms so the second query with the same WHERE will be performed faster.

Answer 4

One more solution to avoid exceptions:

declare
  client_name varchar2(100);
  model_name varchar2(100);

  cursor clients (p_id number) is
    select client_name from client_table where client_id = p_id;

  cursor models (p_id number) is
    select model_name from model_table where model_id = p_id;

begin
  -- variant 1
  for i in clients(123) loop
    client_name := i.client_name;
    exit;
  end loop;

  -- variant 2
  open models(456);
  fetch models into model_name;

  -- if you need to process "no data found" situation: 
  if models%notfound then
     <do something>
  end;
end;

Answer 5

MERGE

This answer gives an example for Oracle.

---

INSERT WHERE NOT EXISTS

I'm not certain this would work in Oracle, I believe it works for general SQL RDBMS like MySQL and PostgreSQL.

INSERT INTO v_name(name) 
   SELECT name FROM person 
   WHERE NOT EXISTS (
      SELECT 1 FROM person WHERE <name/condition> = <value>
   ); 

-- UPDATE ...; -- Add UPDATE statement here to complete an UPSERT query

---

Please express reason for downvotes :)

Answer 6

You can parametrized the query and that will cost you 1 select query at time like

@tablename varchar,
@conditionparameter varchar,

select count(1) into isRowExists from tablename where CONDITION='+conditionparameter+' ;
if(@tablename ='PERSON ' and isRowExists > 0)

select NAME into v_name from PERSON where CONDITION='Something';

elseif (@tablename ='CAR' and isRowExists > 0)

select MODEL into v_model from CAR where CONDITION='Something';

else
raise name_not_found;
end if;

This is generalized idea you can further optimized the above mentioned query.