CODEWITHSUNDEEP
sqlsqlite
Ilya Gazman
Ilya Gazman

Reputation: 32241

Sqlite query all the missing ids

I got two tables in my DB, users and gifts. The user table got id and data, the gifts table got gift_id, user_id and data.

gitst.user_id associated with users.id

How can I get all the user_id from gifts table that not appear in users table?

Upvotes: 0

Views: 182

Answers (2)

Preethi Raju
Preethi Raju

Reputation: 136

select user_id from gifts not exists(select id from users)

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

One way to do it is using the except operator.

select user_id from gifts
except 
select id from users

Except gives you the difference of two sets.

Let's say set A = {1,2,3} B={3,4,5,6}.

A-B = {1,2} because 3 exists in both the sets and 1,2 exist in A but not in B

B-A = {4,5,6} 3 is common to both sets and 4,5,6 exist in B but not in A

You can also do

select user_id from gifts 
where user_id not in (select id from users)

or

select user_id from gifts g
where not exists (select 1 from users where id = g.user_id)

Upvotes: 1

Related Questions