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))

Related Posts

• SQL: INSERT INTO … SELECT … to generate or combine records from existing ones and insert into an existing table
• MySQL: Insert if doesn’t exist otherwise update the existing row
• MySQL: INSERT INTO … SELECT … FROM … Selectively on Table Columns / Fields to Combine Multiple Tables into One
• SQL: CREATE TABLE … SELECT … to generate new table from existing tables
• MySQL: Check if a table exists