Union All but keep only duplicates from one table in T-SQL

Question

I have two table which I would like to union. I need to keep only the duplicates from one of the two tables. I tried to find it, but could not find it anywhere. Hope somebody can help.

For example: Table_1:

ID	Product	Amount
1	A	10
2	B	10
3	C	10

Table_2:

ID	Product	Amount
3	C	9
4	A	100
5	B	100

Desired result:

ID	Product	Amount
1	A	10
2	B	10
3	C	9
4	A	100
5	B	100

So always use the duplicates from table_2. In this example ID 3 is duplicate, so use the duplicate of table_2 with amount 9.

How to realize this with T-SQL? I used the code below:

Select * from Table_1 where Table_1.id != Table_2.id
Union All
Select * from Table_2

But then I receive the error: 'The multi-part identifier "Table_2.ID" could not be bound.'

See my answer. The concept is very staright forward, as when we need distinct rows of a tow tables, we use union. when we use all records we use Union all. — Gudwlk, May 28 '21 at 12:39
Is there anything simpler than:https://stackoverflow.com/a/67740103/8483417 — Andy3B, May 28 '21 at 14:02

score 6 · Answer 1 · answered May 28 '21 at 11:34

6

Use not exists:

Select t1.*
from Table_1 t1
where not exists (select 1 from table_2 t2 where t2.id = t1.id)
Union All
Select t2.*
from Table_2 t2;

answered May 28 '21 at 11:34

Gordon Linoff

1,242,037
58
646
786

score 2 · Accepted Answer · answered May 28 '21 at 13:48

2

Try this:

SELECT  T1.*
FROM    @Table1 T1
WHERE   T1.ID NOT IN (SELECT ID FROM @Table2)
UNION
SELECT  T2.*
FROM    @Table2 T2

answered May 28 '21 at 13:48

Andy3B

444
2
6

One more question, if I need add a column to calculate the amount per product? Sum of Amount / Count of Product (for A it is (100 + 10) / 2 ) How do I wrap this in the T-SQL? – xris23 May 28 '21 at 14:31
SELECT Product, Qty = SUM(1), Amount = SUM(Amount) FROM (***) GROUP BY Product. In between parenthesis, replace *** with prior answer/piece of script. – Andy3B May 29 '21 at 14:54
@xris23 you're better off with `UNION ALL` over `UNION` (which other answers demonstrate). `UNION` implicitly applies a `DISTINCT` to the final dataset, which comes with a performance cost, – Thom A Jun 01 '21 at 16:28

score 1 · Answer 3 · answered May 28 '21 at 11:35

I assume what you want is an EXISTS:

SELECT T1.ID,
       T1.Product,
       T1.Amount
FROM dbo.Table1 T1
WHERE NOT EXISTS (SELECT 1
                  FROM dbo.Table2 T2
                  WHERE T1.ID = T2.ID)
UNION ALL
SELECT T2.ID,
       T2.Product,
       T2.Amount
FROM dbo.Table2 T2;

A FULL OUTER JOIN, however, might also work if ID is unique in both tables:

SELECT ISNULL(T2.ID,T1.ID) AS ID,
       ISNULL(T2.Product,T1.Product) AS Product,
       ISNULL(T2.Amount,T1.Amount) AS Amount
FROM dbo.Table1 T1
     FULL OUTER JOIN dbo.Table2 T2 ON T1.ID = T2.ID;

Gudwlk · Answer 4 · 2021-05-28T12:49:26.907

Union will give you the result. Union will always return unique values always. If you use union all you will get all with duplicates. Your answer would be to use union all.

  SELECT 
  B.ID
 ,B.Product
 ,B.Amount
 FROM
   (

    SELECT 
      A.ID
     ,A.Product
     ,A.Amount
     ,ROW_NUMBER() over (Partition BY  ID, Product order by  Amount ASC) AS [row_num]
    FROM
    (
       SELECT 
          tb_1.*
       FROM tb_1
       UNION ALL 
      SELECT 
        tb_2.* 
      FROM tb_2

     ) AS A
  ) AS B
  WHERE B.[row_num] = 1

Union All but keep only duplicates from one table in T-SQL

4 Answers4