CODEWITHSUNDEEP
sqlsql-servert-sql
John Chrysostom
John Chrysostom

Reputation: 3973

Union - Remove Only Duplicates Created by the Union

I'd like to use a union operator on two tables. I would like the result set to eliminate duplicate values that are created BY THE UNION, but not to eliminate duplicate values that are pre-existing in the tables. Consider this code...

select
    b
from
    (values (1), (2), (2), (3)) as a(b)
union
    select b from (values (2), (4)) as c(b)

I would like this to produce a result set containing two 2's, not three. Of course, currently I get one 2, and if I used UNION ALL instead, I would get three 2's. Anything that can be done?

Upvotes: 2

Views: 62

Answers (2)

T I
T I

Reputation: 9933

Just exclude the rows you don't want from the second table.

WITH t1 AS
(
    SELECT c
    FROM (
        VALUES (1), (2), (2), (3)
    ) t(c)
)
, t2 AS
(
    SELECT c
    FROM (
        VALUES (2), (4)
    ) t(c)
)
SELECT c
FROM t1
UNION ALL
SELECT c
FROM t2
WHERE c NOT IN (SELECT c FROM t1)

Upvotes: 2

Nicholas Carey
Nicholas Carey

Reputation: 74277

You say something like this:

select * from foo
UNION ALL
select * from bar
where not exists ( select * from foo where foo.x = bar.x )

Upvotes: 4

Related Questions