SQL ~ Compare 2 tables ~ Find match and different records

SQL ~ Compare 2 tables ~ Find match and different records

4

Hmm… I have been using SQL for quite some time. But I do not know how to compare 2 tables to find out the different records. For example, I have 2 tables which are table A and table B. Both of them have some similar records. But I want to filter out the different data only. How can I do so? I searched online and discovered the method to do so. It is located in Microsoft SQL Server 2005 Books Online. All we need to do is use EXCEPT function in SQL.

Query to compare 2 tables to find the different

SELECT * FROM TableA EXCEPT SELECT * FROM TableB;

Query to compare 2 tables to find the matches

SELECT * FROM TableA INTERSECT SELECT * FROM TableB;

Hope this will help you too. ;)

  • Esen

    but this seems not workable in Microsoft Access ?!

  • Nguyen Nam

    Yeah .It is great.Thank for a lot

  • sacha nsaka kazadi

    okay but if we have a column that concatenates 3 colums and becomes a unique identifier ; how would you filter out the difference of data from table 1 and table 2 please

    • MMike

      select * from (select concat(a,b,c) as abc , d,e from t1 except select abc,d,e from t2) should work…