Comparing SQL queries between two tables
I have two tables in my database:
table_A: table_B:
id user id user
1 Mike 1 Mike
2 Dan 2 Dan
3 Tom 3 Tom
4 Lina 4 Lina
5 Cynthia
6 Sam
My goal is to determine which users in Table_B do not exist in Table_A based on id. I'm new to SQL and this is what I came up with:
SELECT id FROM Table_B
WHERE B.id NOT IN ( SELECT A.id from Table_A)
Most likely my logic is flawed, so I would be grateful for any guidance.
source to share
You can use sub-query in a sentence predicate WHERE
NOT IN
Then it will id
only return intable_B
subquery
This query returns id
fromtable_A
SELECT table_A.id FROM table_A
It will then be passed to a clause NOT IN
that will return a boolean true
for each iteration of the recordset if not matched. So id
5 and 6 are only returned in the main query.
Final request
SELECT table_B.id, table_B.name FROM table_B WHERE table_B.id NOT IN (SELECT table_A.id FROM table_A);
OR
to select all use column characters *
instead of column lists
SELECT * FROM table_B WHERE table_B.id NOT IN (SELECT table_A.id FROM table_A);
source to share
You are close. It seems to me that the only thing you need to change is the A.ID and B.ID links
SELECT id FROM Table_B B
WHERE B.id NOT IN ( SELECT A.id from Table_A A)
Since you are referring to Table_A as A and table_B as B, you need to create an alias.
You can also use
SELECT id FROM Table_B B
WHERE NOT EXISTS ( SELECT A.id from Table_A A where A.ID = B.ID)
but I would prefer the former.
source to share