SQL Union VS Union All with Demo and How to Union on Specific Column
2 min readApr 28, 2024
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
Bonus : What if we Want only Union on some Column
In such case, you need 2 steps
- GROUP BY
Then you write the logic how to choose the row with duplicated key - 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 UNION
NOT UNION ALL
that will yield the result of duplicated row like below