How do i get the unique records in a UNION where one column is null for one part of the union?

Question

I get a daily feed of products in a staging table. I want to update the actual tables with records from the staging table.

Heres my query.

SELECT NUll, ColumnA, ColumnB FROM stagingTable
UNION
SELECT ID, ColumnA, ColumnB From actualTable

This gives me

NULL 10  100
NULL 20  200
NULL 30  300
1    10  100

I want to remove the duplicate record as that record is already in the actual table.

NULL 10 100

Maybe you should refactor your query to it using join? Or you can take a look to this question https://stackoverflow.com/questions/28432093/remove-duplicate-rows-based-on-one-column-value — Volod, Jul 26 '18 at 07:34

score 1 · Accepted Answer · answered Jul 26 '18 at 10:32

1

I would simply use not exists:

SELECT ID, ColumnA, ColumnB From actualTable
UNION ALL
SELECT NUll, s.ColumnA, s.ColumnB
FROM stagingTable s
WHERE EXISTS (SELECT 1 FROM actualTable t WHERE t.columnA = s.columnA);

answered Jul 26 '18 at 10:32

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Jul 26 '18 at 08:03

I would do it slightly differently from the first answer and use a subquery for the staging table:

SELECT DISTINCT
COALESCE(T1.ID,T2.ID) AS ID
,T1.ColumnA
,T1.ColumnB

FROM
    (
        SELECT
        NULL AS ID
        ,ColumnA
        ,ColumnB

        FROM
        #stagingtable 
    ) AS T1
LEFT OUTER JOIN #actualtable T2
ON T1.ColumnA = T2.ColumnA
    AND T1.ColumnB = T2.ColumnB

How do i get the unique records in a UNION where one column is null for one part of the union?

2 Answers2