Categories
SQL / MySQL Tips and Tutorials

Using SQL to Find records existing in one table but not in another

The 2 tables are built with an identical data structure. Say you have records spread in both tables but neither of them are complete because both contain unique records. You need to consolidate the 2 tables into one with all unique records from each table.

NOT EXISTS clause is just the way to do it in SQL. Let’s try:

SELECT * FROM table2 WHERE NOT EXISTS (SELECT * FROM table1 WHERE id = table2.id)

This will select all records from table2 that don’t exist in table1 by id. To get a complete table of unique records from both table1 and table2, you need to just insert the results here into table1.

INSERT INTO table1 (SELECT * FROM table2 WHERE NOT EXISTS (SELECT * FROM table1 WHERE id = table2.id))

By Yang Yang

Hello, I'm Yang. I build online businesses that please people. Want to join in and post some useful articles on Kavoir.com? Shoot me a message.

3 replies on “Using SQL to Find records existing in one table but not in another”

hi all i have a doubt in sql server
I have two tables like below

table 1:
name number date
a 2 02/03/2011
b 4 12/08/2011
c 2 02/11/2011
d 1 05/11/2011

table 2:

name number date
a 4 25/12/2011
b 3 03/01/2012
f 5 02/02/2012
k 1 02/02/2012

table 2 data will store into table 1

After writing query first table data result will be like

name number date
a 6 25/12/2011
b 7 03/01/2012
c 2 02/11/2011
d 1 05/11/2011
f 5 02/02/2012
k 1 02/02/2012

if column name is same for two tables that no should be add and store in first table table and the record with that name is not exist in first table that record should be add to first table.

I am trying to got that but i didn’t got that .
Plz send me that query if any body knows to [email protected]

Comments are closed.