SQL to fetch data where Unique key matches but the data is different in some other columns between different tables

user_8134936 0
Viewed 51

I have two tables of same structure as below. I am trying to write a query to compare both the tables using the Unique key which is the first column and trying to return values when there is a mismatch in the second column. If the key is not present then no need to consider that data. only if the key is present in both the table then we have compare it. 

Table A  
ColumnA ColumnB  
A         1  
B         2  
C         2  
D         8  

Table B  
ColumnC ColumnD  
A         1  
B         3  
C         5  
F         4

For example the output of the above table when comparing Table A with B should be 

B         2
C         2

and when comparing Table B with A it should be 

B         3
C         5

Ideally the difference in the base table should come. I have tried Joins and Unions but I am not able to fetch the data as mentioned above.

mysql sql
3 Answers
  • Since you want only those rows which has matching FK values in both the tables, we simply need to use INNER JOIN.
  • Now, we can simply consider the unmatching rows by using WHERE .. <> ..

When comparing Table A against Table B, we can get Table A rows only: 

SELECT
 tA.* 
FROM tableA AS tA
JOIN tableB AS tB 
  ON tB.ColumnC = tA.ColumnA
WHERE tB.ColumnD <> tA.ColumnB

When comparing Table B against Table A, simply fetch the rows from Table B only: 

SELECT
 tB.* 
FROM tableA AS tA
JOIN tableB AS tB 
  ON tB.ColumnC = tA.ColumnA
WHERE tB.ColumnD <> tA.ColumnB
user_24693080

I would do :

SELECT t.*
FROM tablea t
WHERE EXISTS (SELECT 1 FROM tableb t1 WHERE t1.cola = t.cola AND t1.colb <> t.cold);

Same would be for second version just need to swipe the table names.

user_55294450

use EXISTS and union all

SELECT t.*
FROM tablea t
WHERE EXISTS (SELECT 1 FROM tableb t1 WHERE t1.cola = t.cola AND t1.colb <> t.colb)
union all    
SELECT t.*
FROM tableb t
WHERE EXISTS (SELECT 1 FROM tablea t1 WHERE t1.cola = t.cola AND t1.colb <> t.colb)
user_68085830
Related