UNION and UNION ALL: Combine compatible results
Sometimes the information lives in different tables but represents the same type of entity. We can have current orders and historical orders, or results coming from two different filters.
UNION and UNION ALL place one set below the other. They do not relate columns like a JOIN; They add rows to a common result.
The idea
Each query must produce the same number of columns in the same order, with compatible types: types that SQL Server can convert to a common type. The final names come from the first query.
UNION ALL: keep every row
UNION ALL combines results without looking for duplicates. If the same row appears in both sets, it remains twice.
SELECT Columna1, Columna2
FROM dbo.TablaActual
UNION ALL
SELECT ColumnaCompatible1, ColumnaCompatible2
FROM dbo.TablaHistorica;UNION: remove duplicates from the combined set
UNION applies the same shape requirements, but keeps one copy of each repeated complete row.
SELECT Columna1, Columna2
FROM dbo.TablaActual
UNION
SELECT ColumnaCompatible1, ColumnaCompatible2
FROM dbo.TablaHistorica;Example: current and historical orders
CAST(expresión AS tipo) converts a value to the indicated type. Here you set both labels to NVARCHAR(10) so that the Origen column has a known length on both sides of the join.
SELECT
p.IdPedido,
p.IdCliente,
p.FechaPedido,
p.Estado,
CAST(N'Actual' AS NVARCHAR(10)) AS Origen
FROM dbo.Pedido AS p
UNION ALL
SELECT
ph.IdPedidoOriginal,
ph.IdCliente,
ph.FechaPedido,
ph.Estado,
CAST(N'Histórico' AS NVARCHAR(10)) AS Origen
FROM dbo.PedidoHistorico AS ph
ORDER BY FechaPedido DESC;The Origen column allows you to distinguish sets that would otherwise have the same shape. The ORDER BY is written only once, at the end of the join.
When to use UNION?
If two queries can produce exactly the same row and our intention is to keep it only once:
SELECT IdCliente
FROM dbo.Pedido
WHERE Estado = 'Confirmado'
UNION
SELECT IdCliente
FROM dbo.Pedido
WHERE Estado = 'Enviado';The result contains each identifier only once. However, if we know that the sets do not overlap or if the repetitions are meaningful, UNION ALL avoids the additional work of detecting duplicates.
Duplicates are compared for the entire row
UNION does not dedupe using only the key we consider important. Compare all selected columns. Two rows with the same IdCliente and different dates are still different.
If we need to choose a specific row per entity, we may need ROW_NUMBER, which assigns a position to each row according to an order, an aggregate, or another explicit rule.
What should be checked
Column order can produce silent interpretation errors when two positions accept compatible types but represent different data. Write both lists in the same format and use explicit conversions—conversions stated in the query instead of chosen automatically by SQL Server—when length or precision must be controlled.
We also check whether deduplication is really part of the need. Using UNION out of habit can hide a duplication originating from another part of the query.