CODEWITHSUNDEEP
mysqlsql
chx
chx

Reputation: 11760

Which values are missing in SQL from a list?

I have a list of values, say 1,2,3,4,5,6. I have a SQL table with a field storing these values but only three rows, say, 2,4,5. What sort of query will return three rows 1,3,6 for me? (storing all six in another table and left join'ing is cheating.)

Upvotes: 6

Views: 18313

Answers (6)

Adam Mátl
Adam Mátl

Reputation: 166

with Mysql 8 you can use JSON_TABLE and select table from JSON array of values

SELECT *
      FROM
     JSON_TABLE(
       '[1,2,3,4,5,6]',
       '$[*]' COLUMNS( id INT PATH '$' ERROR ON ERROR )
     ) as v
 left join the_real_table t on t.id = v.id
where t.id is null;

Upvotes: 2

user330315
user330315

Reputation:

You didn't state your DBMS, so this is the ANSI SQL Version:

with the_values (id) as ( 
  values (1),(2),(3),(4),(5),(6)
)
select v.id 
from the_values v
  left join the_real_table t on t.id = v.id
where t.id is null;

Upvotes: 8

TelJanini
TelJanini

Reputation: 835

You could also try using EXCEPT (similar to MINUS in Oracle):

(SELECT 1
UNION
SELECT 2
UNION 
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6)
EXCEPT
(SELECT 2
 UNION
 SELECT 3
 UNION
 SELECT 4)

Or, more relevant to your example:

(SELECT 1
UNION
SELECT 2
UNION 
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6)
EXCEPT
(SELECT Field FROM Table)

where Field contains 2, 4, and 5.

Upvotes: 3

tenfour
tenfour

Reputation: 36896

The not in operator will do the check you want.

declare @allValues table (value int)
insert @allValues values (1)
insert @allValues values (2)
insert @allValues values (3)
insert @allValues values (4)
insert @allValues values (5)
insert @allValues values (6)

declare @someValues table (value int)
insert @someValues values (2)
insert @someValues values (4)
insert @someValues values (5)

select
    *
from
    @allValues
where
    value not in (select value from @someValues)

Another method which is probably faster, using joins:

select
    av.value
from
    @allValues av
    left join @someValues sv on (av.value = sv.value)
where
    sv.value is null

Upvotes: 1

KeithS
KeithS

Reputation: 71573

It's not cheating if it does the job. Set up a temp table or table variable with all the possible rows, then use a WHERE NOT EXISTS or a LEFT OUTER JOIN combined with WHERE TableVariableID IS NULL.

Upvotes: 0

Marcus Recck
Marcus Recck

Reputation: 5065

You could use a NOT IN clause.

SELECT column FROM table WHERE column NOT IN (2,4,5)

Upvotes: -3

Related Questions