IF Statement Using IN Operator On SELECT Results

Question

I am trying to evaluate IF a value exists in the results of a SELECT statement. I know you can do something like:

IF v_var IN ('A', 'B') 
THEN 
  DBMS_OUTPUT.PUT_LINE('True');
END IF;

However, I would like to do something like:

IF v_var IN (SELECT x FROM DUAL)
THEN 
  DBMS_OUTPUT.PUT_LINE('True');
END IF;

Is there a syntax legal way to accomplish this?

Answer 1

I would typically handle this sort of logic by running a count(*) query on the table to check for a row that matches the value you are looking for. Then count > 0 tells you the value exists, and count = 0 tells you it does not. So the code would look like:

  select count(*) into v_cnt from tbl where x = v_var and rownum < 2;
  IF v_cnt > 0 THEN
    DBMS_OUTPUT.PUT_LINE('True');
  END IF;

Notice that the optimizer is free to use relevant access structures (e.g. indexes, partitioning, zone maps) to efficiently execute this query. And the rownum < 2 predicate stops the query as soon as it finds one match for v_var; we do this because EXISTS means we don't care exactly how many matches we have, we just need to distinguish zero matches vs non-zero matches.