This method is faster and performs well on the large volume of data. The other faster method is to use the NOT EXISTS in WHERE clause of the query. The only drawback of using JOIN is, it cannot compare the NULL values, and you should use the NVL on the column that may have null values in it. Select case when A.col1 = B.col1 then ‘Match’ else ‘Mismatch’ end as col1_cmpr,Ĭase when A.col2 = B.col2 then ‘Match’ else ‘Mismatch’ end as col2_cmpr, …. In this approach you can join the two tables on the primary key of the two tables and use case statement to check whether particular column is matching between two tables. This is the easiest but user has to do some additional work to get the correct result. The only drawback with using UNION and MINUS is that the tables must have the same number of columns and the data types must match. You can quickly check how many records are having mismatch between two tables. Select Id_pk, col1, col2.,coln from table2 Select Id_pk, col1, col2.,coln from table1 It returns all rows in table 1 that do not exist or changed in the other table. You can compare the two similar tables or data sets using MINUS operator. Related reading: Steps to Optimize SQL Query Performance Compare Two Table using MINUS You can quickly verify the differences between two tables. The above query returns the all rows from both tables as old and new. Select Id_pk, col1, col2.,coln from table2, 'New_tbale' Select Id_pk, col1, col2.,coln from table1, ‘Old_table’ It allows quickly checking what are the data missing or changed in either table. It also handles the NULL values to other NULL values which JOIN or WHERE clause doesn’t handle. UNION allows you to compare data from two similar tables or data sets. Below are some of the methods you can use to compare two tables in SQL.
0 kommentar(er)
