Compare two SQL tables and return missing ids?
NickName:MilMike Ask DateTime:2011-11-03T23:59:09

Compare two SQL tables and return missing ids?

I have two simple tables: (here only the "id" column)

table1:

id
1
2
3
4

table2:

id
2
4

the sql query should compare the two tables for missing "id" in table2 and return: 1,3

any ideas? :) TY

Copyright Notice：Content Author:「MilMike」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/7997975/compare-two-sql-tables-and-return-missing-ids

Answers
James Hill 2011-11-03T16:00:20

There are several ways to skin this cat:\n\nSELECT table1.ID\nFROM table1\nWHERE table1.ID NOT IN(SELECT table2.ID FROM table2)\n\n\nOr you could use a left outer join:\n\nSELECT table1.ID\nFROM table1\nLEFT OUTER JOIN table2 ON table1.ID = table2.ID\nWHERE table2.ID IS NULL\n",

D'Arcy Rittich 2011-11-03T16:00:30

select t1.*\nfrom table1 t1\nleft outer join table2 t2 on t1.id = t2.id\nwhere t2.id is null\n",

Products recommended by Amazon
More >>>
SanDisk 128GB microSDXC-Card, Licensed for Nintendo-Switch - SDSQXAO-128G-GNCZN
SanDisk 128GB microSDXC-Card, Licensed for Nintendo-Switch - SDSQXAO-128G-GNCZN
 HUANUO Dual Monitor Stand - Adjustable Spring Monitor Desk Mount Swivel Vesa Bracket with C Clamp, Grommet Mounting Base for 13 to 27 Inch Computer Screens - Each Arm Holds 4.4 to 14.3lbs
HUANUO Dual Monitor Stand - Adjustable Spring Monitor Desk Mount Swivel Vesa Bracket with C Clamp, Grommet Mounting Base for 13 to 27 Inch Computer Screens - Each Arm Holds 4.4 to 14.3lbs
 AMD Ryzen 5 5600X 6-core, 12-Thread Unlocked Desktop Processor with Wraith Stealth Cooler
AMD Ryzen 5 5600X 6-core, 12-Thread Unlocked Desktop Processor with Wraith Stealth Cooler
 Timberland Men's 6-Inch Premium Waterproof Boot
Timberland Men's 6-Inch Premium Waterproof Boot
More about “Compare two SQL tables and return missing ids?” related questions
Compare two SQL tables and return missing ids?

I have two simple tables: (here only the "id" column) table1: id 1 2 3 4 table2: id 2 4 the sql query should compare the two tables for missing "id" in table2 and re

Show Detail
MySQL- Compare Customer IDs from two tables to determine who made no purchases

I have two tables in SQL, one with information on customers and orders placed by them (columns include customerid, contactname, orderid, quantity, to name a few). My second table is simply a list ...

Show Detail
Oracle SQL compare two tables data

I have two tables A,B in my oracle db and I want to compare data of both tables based on unique field (userid) but B table contains user IDs as Puserid (P appended for all IDs) How to use where

Show Detail
Compare two sql server tables

Can anyone suggest me how to compare two database tables in sql server and return the rows in the second table which are not in the first table. The primary key in both the tables is not the same. ...

Show Detail
Tool to compare two different tables

I need to compare structure of approx. 50 tables in SQL Server 2005, and write T-Sql script to make them equal. Each table has its copy in the same database, the only difference being the name and

Show Detail
Compare Two SQL identical Table to find missing records

I am working on SQL 2008. I have two identical tables with same column names. On Table2, i am missing some records. Some records got deleted in the Table2. I have to compare Table1 and Table2 and

Show Detail
compare two columns from two different tables in oracle sql for differences

Hi first time posting for SQL, I need to compare two different columns from two different tables in SQL. For example there is two tables and each have one column in them and I need to compare the...

Show Detail
Compare two tables for a matching value from the respective columns and identify records missing

I have two tables and data like below: I want to compare two table's columns "Type and MatchType" and identify the Ids of first table where Type is missing in MatchType. "Type" and "MatchType" are...

Show Detail
Compare two tables in MS Access SQL query

I have two Excel tables (A and B) with names of staff. I want to compare these two tables in MS Access. I have an Access query which compares a field like [Lastname, Firstname] and gave me as resu...

Show Detail
Compare Two Pivot Tables

I have two pivot tables, one in Column A, one in Column B, and they are formatted the same way. The pivot tables have a list of Users, and under each User a list of IDs. Is there a way I can comp...

Show Detail