CODEWITHSUNDEEP
mysqlsqldatabaseoracle-database
Sunil K
Sunil K

Reputation: 3

Query which should return the non existing values in table

I have

Lets say among 10 keys which I have, if there are 8 keys found in the table.

I need the output of the remaining 2 keys which is not present in the table.

Eg: i have 10 empl id's which i need to query in Empl table. Empl table has 100 or even more rows. Among 10 empl id which i have , only 8 are there in empl table. I need to get that remaining 2 empl ids which are not there in empl table. NOTE: if you use not in , it will give all other empl ids from empl table. But i need only those two which are not present.

Upvotes: 0

Views: 107

Answers (3)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

SELECT *
FROM Table10 T
WHERE not exists ( SELECT 1
                   FROM Table100 Q
                   WHERE T.ID = Q.ID )

Upvotes: 0

Julio Soares
Julio Soares

Reputation: 1190

select * from table where my_key not in (select distinct my_key from the_other_table)

Upvotes: 0

juergen d
juergen d

Reputation: 204884

To make the query a little shorter I give you an example for 3 keys you have to check in a table

select k.*
from
(
  select 1 as pk
  union all
  select 3
  union all
  select 7
) k
left join your_table t on t.id = k.pk
where t.id is null

Upvotes: 1

Related Questions