SQL Union VS Union All with Demo and How to Union on Specific Column

Differences

UNION ALL ignore duplicated row

Demo

select 1, 'foo'
union select 2, 'foo'
union select 2, 'bar'
union select 1, 'foo'
union select 3, 'baz'

select 1, 'foo'
union all select 2, 'foo'
union all select 2, 'bar'
union all select 1, 'foo'
union all select 3, 'baz'

Seeing that the result is unique considering all column as key

UNION (left) VS UNION ALL (right)

Bonus : What if we Want only Union on some Column

In such case, you need 2 steps

1. GROUP BY
   Then you write the logic how to choose the row with duplicated key
2. LEFT JOIN

Example

We think we select first column as key to union

and pick the MAX(y) to represent column of duplicated.

with sub as (
 select 1 as k, 'foo' as v, 2024 as y
 union select 2 as k, 'foo' as v, 2024 as y
 union select 2 as k, 'bar' as v, 2023 as y
 union select 1 as k, 'foo' as v, 2024 as y
 union select 3 as k, 'baz' as v, 2024 as y
)
select sub.k, sub.v, sub. y from (
 select k as k, max(y) as max_y from sub group by k
) as sub2
left join sub on sub2.k = sub.k and sub2.max_y = sub.y

Warning : Use UNION

Note that we use UNIONNOT UNION ALLthat will yield the result of duplicated row like below