Select a Category

How to find difference in two datasets with SQL?

Problem

Need to make sure that two datasets are the same. One traditional way to compare them is importing their data into an Excel spreadsheet. Then one can use Excel function to find the difference.

Better Solution

Oracle provides MINUS operator, which can subtract second dataset from first dataset. For example:

Select * From (Select Name1, Name2 fromĀ  Table_A) MINUS (Select Name1, Name2 from Table B)

If the statement returns nothing, then every row in first dataset has a corresponding row in second dataset. If the query returns some data, the result data is the data that are in dataset1 but not in dataset 2.

However, to make sure second dataset does not contain more rows than first data set, a second test should be used:

Select * From (Select Name1, Name2 fromĀ  Table_B) MINUS (Select Name1, Name2 from Table B)

Only both queries return nothing then one can be sure that both datasets are equal.

Credit

  1. MINUS operator on Tech On the Web

 

 

Comments are closed.