CODEWITHSUNDEEP
sqlsql-serverjoin
Samer Nachabé
Samer Nachabé

Reputation: 943

Finding unmatched rows of 2 tables in SQL

I have 2 tables with 3 columns that are suppose to have the same information. I would like have a query that selects only the rows that don't have a complete row match. Below is an example of the 2 tables I would like to match:

Table 1

   ID   FPRICE  FPRODUCT
   1      1       A
   2      2       B
   3      3       C
   4      4       D
   5      5       F

Table 2

   ID   TPRICE  TPRODUCT
   1      1       A
   2      2       B
   3      3       C
   4      5       D
   6      6       E

Desired Output:

   ID   FPRICE  FPRODUCT   TPRICE   TPRODUCT
   4      4       D          5          D
   5      5       F          NULL       NULL
   6     NULL     NULL       6          E

Upvotes: 0

Views: 3356

Answers (6)

Karl Kieninger
Karl Kieninger

Reputation: 9149

Easier to verify if we build some DDL and fillwith sample data, but I think this would do it. It takes a full join to find records with a partial match and then filters out records with a full match.

sqlfiddle.com

CREATE TABLE Table1 (ID INT, FPRICE INT, FPRODUCT CHAR(1))  
INSERT INTO Table1 (ID,FPRICE,FPRODUCT) VALUES
   (1,      1,       'A')
  ,(2,      2,       'B')
  ,(3,      3,       'C')
  ,(4,      4,       'D')
  ,(5,      5,       'F')   


CREATE TABLE TABLE2 (ID INT, TPRICE INT, TPRODUCT CHAR(1))
INSERT INTO Table2 (ID,TPRICE,TPRODUCT) VALUES
   (1,      1,       'A')
  ,(2,      2,       'B')
  ,(3,      3,       'C')
  ,(4,      5,       'D')
  ,(6,      6,       'E')   

SELECT * 
  FROM Table1 t1
       FULL JOIN 
       Table2 t2 ON t1.ID = t2.ID  
                 --EDIT: remove to exactly match the desired output
                 --OR t1.FPRICE = t2.TPRICE  
                 --OR t1.FPRODUCT = t2.TPRODUCT
 WHERE NOT (    t1.ID = t2.ID 
            AND t1.FPRICE = t2.TPRICE 
            AND t1.FPRODUCT = t2.TPRODUCT)
       OR (   COALESCE(t1.ID,t1.FPRICE,T1.FPRODUCT) IS NULL
           OR COALESCE(t2.ID,t2.TPRICE,T2.TPRODUCT) IS NULL)

Upvotes: 1

Premks
Premks

Reputation: 21

I would format the answer from Michael1105 as:

SELECT ISNULL(t1.ID,t2.ID),t1.FPRICE,t1.FPRODUCT,t2.TPRICE,t2.TPRODUCT FROM Table_1 t1 FULL JOIN Table_2 t2 ON t2.ID = t1.ID WHERE t2.TPRICE
<>t1.FPRICE OR t2.TPRODUCT
  <>t1.FPRODUCT OR t2.TPRICE IS NULL OR t1.FPRICE IS NULL OR t2.TPRODUCT IS NULL OR t1.FPRODUCT IS NULL

for your exact requirement.

Upvotes: 0

Khamill
Khamill

Reputation: 51

Can you try this query?

SELECT DISTINCT t1.ID t1.FPRICE t1.FPRODUCT
from Table1 t1 LEFT JOIN Table2 ON 
Table1.ID = Table2.ID AND Table1.FPRICE = Table2.TPRICE AND Table1.FPRODUCT= Table2.TPRODUCT
WHERE 
Table1.ID is NULL or Table1.FPRICE is NULL or Table1.FPRODUCT is NULL 
or Table2.TPRICE is NULL or Table2.TPRODUCT is NULL

Upvotes: 0

Boomit
Boomit

Reputation: 187

SELECT *
FROM Table1 t1
FULL JOIN Table2 t2 ON t2.ID = t1.ID
WHERE
    t2.TPRICE <> t1.FPRICE
    OR t2.TPRODUCT <> t1.FPRODUCT
    OR t2.TPRICE IS NULL
    OR t1.FPRICE IS NULL
    OR t2.TPRODUCT IS NULL
    OR t1.FPRODUCT IS NULL

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

It looks like you want to match on the id's and then compare the values:

select t1.id, t1.fprice, t1.fproduct, t2.fprice, t2.fproduct
from Table1 t1 full join
     Table2 t2
     on t1.ID = t2.ID 
where (t1.ID is null or t2.ID is null) or -- no match as all
      (t1.FPRICE <> t2.FPRICE or
       t1.FPRODUCT <> t2.FPRODUCT
      );

The where clause is a bit more complicated if the column values could be NULL, but your sample data does not have any examples of NULLs.

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Use full join:

select * 
from Table1 t1
full join Table2 t2 on t1.ID = t2.ID and 
                       t1.FPRICE = t2.FPRICE /*and 
                       t1.FPRODUCT = t2.FPRODUCT*/
where t1.ID is null or t2.ID is null

Upvotes: 0

Related Questions