CODEWITHSUNDEEP
sqloracle-database
ahjmorton
ahjmorton

Reputation: 965

Determine which values are missing from an Oracle database column

I have a list of values and I want to query which values in that list DO NOT appear in a particular column in an Oracle database (not sure which version).

So for example if my list of values is A,B,C and I have a table as below :

--------
|COLUMN|
--------
|     C|
|     A|
--------

The result I would expect would be B.

So far my approach has been a SQL query similar to the below :

SELECT <<List of values SQL, not sure what goes here>>
EXCEPT
SELECT column FROM table

However I do not know what the SQL for the first statement looks like. So far I came up with :

SELECT "A","B","C" FROM dual

But this doesn't have the desired effect as it creates 3 columns

Another point to mention is that in the actual problem there are around 100 entries in the list to search, not the three in the toy example above.

Upvotes: 1

Views: 86

Answers (2)

rusk
rusk

Reputation: 300

Dirk's solution works fine. However, Regular Expressions can also be used for achieving this goal for Oracle 10g and above:

SELECT trim(regexp_substr(str, '[^,]+', 1, level)) str
FROM (SELECT 'A,B,C,D' str from dual)
CONNECT BY instr(str, ',', 1, level - 1) > 0
Minus
Select column from table

Upvotes: 0

DirkNM
DirkNM

Reputation: 2664

Maybe this helps:

WITH static_list AS (
  SELECT 'A' AS v FROM dual UNION ALL
  SELECT 'B' AS v FROM dual UNION ALL
  SELECT 'C' AS v FROM dual
)
SELECT v FROM static_list 
 MINUS (SELECT column 
          FROM table);

Upvotes: 3

Related Questions