CODEWITHSUNDEEP
sql-server
user1296762
user1296762

Reputation: 512

Find records in one table that aren't in another

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

Upvotes: 0

Views: 1645

Answers (2)

Andriy M
Andriy M

Reputation: 77717

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.

Upvotes: 0

podiluska
podiluska

Reputation: 51514

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

Upvotes: 1

Related Questions