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?

Are you talking about a specific column or just anywhere in the output of a `select` statement? — Radagast, Sep 16 '21 at 21:01
Are you really trying to select from dual? If so, I don't really see the difference — Andrew, Sep 16 '21 at 21:02
@Andrew Not actually trying to select from dual, that is just a simplified example. — Acroyear, Sep 16 '21 at 21:21
@PhilCoulson It would be a specific column. Similar to what you might do in a where clause. — Acroyear, Sep 16 '21 at 21:24

score 3 · Answer 1 · answered Sep 16 '21 at 22:37

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.

IF Statement Using IN Operator On SELECT Results

1 Answers1