CODEWITHSUNDEEP
sql-serversql-server-2008
user3617609
user3617609

Reputation: 23

How to find missing rows?

I have two identical tables A and B. And both the tables have same fields, as an example Table A (bin, storage, plant) and B (bin, storage, plant). But when I checked the data, table A has 5238 rows and B has 5249 rows. So I dont know which 11 rows are missing. I need help to write a query where I can find those missing rows.

Thanks for the help in advance.

Upvotes: 2

Views: 317

Answers (2)

juergen d
juergen d

Reputation: 204766

select *
from tableA
full outer join tableB on tableA.bin = tableB.bin
where tableA.bin is null or tableB.bin is null

SQL-Server allows a full outer join. You can select all records from both table and limit the result to those where the join does not find matches on the other table.

Upvotes: 1

Paul Facklam
Paul Facklam

Reputation: 1633

Can use the EXCEPT command for your problem:

SELECT bin 
  FROM tableB
EXCEPT
SELECT bin 
  FROM tableA;

Shows all bins which are in tableB but not in tableA.

Upvotes: 4

Related Questions