How to check efficiently if a column contains given values?-CodePudding

I have a table TBL1 which contains columns, among them COL1.

I want to check as efficiently as possible (quick computation, small result) if the values "foo", "bar" and "bar2" are found in COL1, and report which are missing.

How can I do that?

EDIT:

The version is "19.00.0000", the table structure and index might vary as this is meant to be applied on various cases (i.e. COL1 might be indexed or not).

CodePudding user response：

Put your words in a separate table/cte that you LEFT JOIN tbl1. Something like:

with cte (c1) as (select 'foo' from dual
                  union all
                  select 'bar' from dual
                  union all
                  select 'bar2' from dual)
select c1
from cte
left join tbl1 on cte.c1 = tbl1.col1
where tbl1.col1 is null;

Make sure to have a tbl1.col1 index.

Demo: https://dbfiddle.uk/mjAU2YPP

CodePudding user response：

Another option is to use the minus set operator:

SQL> WITH
  2     tbl1 (col1)                      --> this is your table (you don't
  3     AS                                   have to type it here, as a CTE)
  4        (SELECT 'foo' FROM DUAL         
  5         UNION ALL
  6         SELECT 'xyz' FROM DUAL),
  7     cte (c1)                         --> this is table that contains
  8     AS                               --  values you're checking; could
  9        (SELECT 'foo' FROM DUAL       --  be "real" table, or a CTE as in 
 10         UNION ALL                    --  this example
 11         SELECT 'bar' FROM DUAL
 12         UNION ALL
 13         SELECT 'bar2' FROM DUAL)
 14  SELECT c1 FROM cte                  --> finally, the query itself
 15  MINUS
 16  SELECT col1 FROM tbl1;

C1
----
bar
bar2

SQL>