SQL query comparison between two tables

Question

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 aim is to identify which users in Table_B do not exist in Table_A based on id. I'm new in 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 wrong, so i'd appreciate any guidance please.

This other [question](http://stackoverflow.com/questions/4602083/sql-compare-data-from-two-tables) might be similar. — Tom, Jul 23 '15 at 06:00

gvgvgvijayan · Accepted Answer · 2015-07-23T06:27:19.863

1

You can use sub-query in WHERE clause predicate NOT IN Then it will return the id present in table_B only

Sub-Query

This query return id from table_A

SELECT table_A.id FROM table_A

Then it will be passed to NOT IN clause which will return boolean true on every record set iteration if not matched. So id 5 and 6 only return in the main query.

Final Query

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 column use symbol * instead of column lists

SELECT * FROM table_B WHERE table_B.id NOT IN (SELECT table_A.id FROM table_A);

edited Jul 23 '15 at 06:27

answered Jul 23 '15 at 06:18

gvgvgvijayan

1,851
18
34

if i want to extract the user also with the id do i do : SELECT table_B.id AND table_B.user FROM table_B WHERE table_B.id NOT IN (SELECT table_A.id FROM table_A); – dan Jul 23 '15 at 06:23
@dan Learn using some good sql book before using mysql my suggestion goes to http://www.db-book.com/ or else you don't have time to read this book, then you can directly jump into mysql go for this book https://www.murach.com/shop/murach-s-mysql-detail a bit old but its easy and clear to learn. – gvgvgvijayan Jul 23 '15 at 06:30

score 0 · Answer 2 · answered Jul 23 '15 at 05:58

0

You can use EXISTS function.

Select * from table_B where Not EXISTS (select * from table_A)

answered Jul 23 '15 at 05:58

Mukesh Kalgude

4,814
2
17
32

but isn't the * a slow solution, when you want one element from a table? – dan Jul 23 '15 at 05:59
Mysql run this query. More detail to refer this link https://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html – Mukesh Kalgude Jul 23 '15 at 06:00

Jower · Answer 3 · 2015-07-23T14:50:16.857

0

You are close. It seems to me that the only thing you need to change is the A.ID and B.ID reference

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 the alias name.

You could 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 first one.

edited Jul 23 '15 at 14:50

answered Jul 23 '15 at 06:03

Jower

565
2
8

I got token mismatch, what could be the problem? – dan Jul 23 '15 at 06:14

SQL query comparison between two tables

3 Answers3