CODEWITHSUNDEEP
sqloracle-database
Moh-Aw
Moh-Aw

Reputation: 3018

Find missing value in table from given set

Assume there is a table called "allvalues" with a column named "column". This column contains the values "A" to "J" while missing the "H".

I am given a set of values from "G" to "J".

How can I query the table to see which value of my set is missing in the column?

The following does not work:

select * from allvalues where column not in ('G', 'H', 'I', 'J')

This query would result in A, B, C, D, E, F, H which also contains values not included in the given set.

Obviously in such a small data pool the missing value is noticeable by eye, but imagine more entries in the table and a bigger set.

Upvotes: 0

Views: 58

Answers (3)

Nikhil
Nikhil

Reputation: 3950

this will work:

    select * from table1;
    G
    H
    I
    J

    select * from table1
    minus
    (select * from table1
    intersect 
    select column from allvalues
    )
sample input:

select * from  ns_table10;
G
H
I
J
SELECT * FROM ns_table11;
A
B
C
D
E
F
G
J
I
select * from ns_table10
minus
(select * from ns_table10
intersect 
select * from ns_table11
);

output:
H

Upvotes: 0

Wouter
Wouter

Reputation: 383

Depends on which SQL syntax you can use, but basically you want to check your table allvalues + the extra values.

eg:

SELECT *                                 
FROM ALLVALUES                           
WHERE COLUMN NOT IN (                    
( select s.column from allvalues s )     
and column not in ('G', 'H', 'I', 'J')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You need to start with a (derived) table with the values you are checking. One explicit method is:

with testvalues as (
      select 'G' as val from dual union all
      select 'H' as val from dual union all
      select 'I' as val from dual union all
      select 'J' as val from dual 
     )
select tv.val
from testvalues tv
where not exists (select 1 from allvalues av where av.column = tv.val);

Often, the values originate through a query or a table. So explicitly declaring them is unnecessary -- you can replace that part with a subquery.

Upvotes: 2

Related Questions