31 August 2007

SQL compare two tables

Difference in data stored in both tables (compare the difference between two tables)? You can use SQL to see rows which don't have a match in one table or the other:

Select col_a,col_b from table1 
where not exists 
    (select * from table2 
     where table1.col_a = table2.col_a and 
           table1.col_b = table2.col_b)

Repeat for table2.

further notes: --http://windowsitpro.com/Articles/ArticleID/14368/14368.html?Ad=1
--http://webmasterworld.com/databases_sql_mysql/3241540.htm

Here is a way of comparing 2 tables that I tried at work:

-- DROP my new temp table if exists:
DROP TABLE ##EricsTempUnionOfMonthlyAndBiweekly

-- UNION the monthly & biweekly tbls into a temp table:
SELECT * INTO ##EricsTempUnionOfMonthlyAndBiweekly 
FROM (SELECT * FROM NISTempLowLevelMonthly 
UNION SELECT * FROM NISTempLowLevelBiweekly) un

-- Verify it:
SELECT * FROM ##EricsTempUnionOfMonthlyAndBiweekly --18347rows, yes this was a UNION of monthly & biweekly tables.

-- LEFT OUTER JOIN which means gives me the differences:
SELECT DISTINCT * FROM ##EricsTempUnionOfMonthlyAndBiweekly LEFT OUTER JOIN NISTemp_Qtrly_Detail 
ON NISTemp_Qtrly_Detail.SocSecNumber = ##EricsTempUnionOfMonthlyAndBiweekly.SocSecNumber -- 72573 rows
 

1 comment:

Decompiled said...

I found this very useful, thank you.

Select DETORDER from WHORDDT
where not exists
(select * from WHORDHD
where WHORDDT.DETORDER = WHORDHD."ORDER")

header adsense code