Find records in one table that aren't in another

Question

Ive got two tables. One has 102845 records, the other has 98496. I need to find the records that appear in the bigger table but not in the smaller table (4349). These are how I create the numbers:

--98496

drop table #test2 select a.*, B.Delq_Sep12, b.Bal_Sep12, b.Queue_Sep12 into #test2 from #test4 b join pcd a on (a.ACCOUNT_NUMBER = B.account_number)

--102845

drop table #test1 select a.*, B.Delq_Sep12, b.Bal_Sep12, b.Queue_Sep12, into #test1 from #test4 b left join pcd a on (a.ACCOUNT_NUMBER = B.account_number)

Thanks

podiluska · Accepted Answer · 2012-10-16 10:41:32Z

select * from #test1 except select * from #test2

Of course, this assumes that all records in #test2 are in #test1

If you want to check the reverse, just reverse the query.

select * from #test2 except select * from #test1

Andriy M · Accepted Answer · 2012-10-17 15:25:48Z

Apparently, your first query gets only matching rows from the two tables. The second one gets all rows from b while also pulling data from a where there is a match, and if there was no match, the a columns get filled with NULLs.

Now, the difference between the two result sets would effectively be the non-matching rows of b. And to get those rows, you could just use your second query with a NULL check like this:

select a.*, B.Delq_Sep12, b.Bal_Sep12, b.Queue_Sep12, from #test4 b left join pcd a on (a.ACCOUNT_NUMBER = B.account_number) where a.ACCOUNT_NUMBER is null

That is, no #test1 or #test2 is needed, just query for the non-matching rows directly.

Collectives™ on Stack Overflow

Find records in one table that aren't in another

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related