The following query
declare @datCed DATE = '2022-01-31'
;with t as (
SELECT 'CED' AS src, Costo
FROM DM_VRECostPerCid_CED
WHERE DatCed = @datCed
UNION
SELECT 'ADJ' AS src, Importo
FROM dbo.DM_PY_Adjusted
WHERE DatCed = @datCed
)
select src, sum(costo)
from t
group by src
;with t as (
SELECT 'CED' AS src, CodAzi, CID, DatCed, CdC, CoGe, Costo
FROM DM_VRECostPerCid_CED
WHERE DatCed = @datCed
--UNION
-- SELECT 'ADJ' AS src, CodSoc, CID, DatCed, CdC, CoGe, Importo
-- FROM dbo.DM_PY_Adjusted
-- WHERE DatCed between '2022-01-31' AND '2022-12-31'
)
select src, sum(costo)
from t
group by src
return two very different values: the first one returns 18,660,541.37, and the second returns 31,242,156.23.
Now, the only difference between the queries is that the UNION and the following SQL statements are commented.
In my ignorance, I was expecting that the final dataset will be compounds from the two subqueries run independently: in fact, the execution plan gives me the same information, each query run independently and then there is an "Concatenation" operation
Can you explain me what is the interaction in a UNION statement between the subqueries that build the final dataset? As usual, there will be an easy and evident reason, but I cannot figure what it could be :(
Environment: MSSQL 2008R2 The view DM_VRECostPerCid_CED is defined as
CREATE VIEW [dbo].[DM_VRECostPerCid_CED]
AS
SELECT v.CodAzi, v.CID, v.Liv, v.VRE, v.CoGe, v.DatCed, vg.IDGruppo, SUM(v.Importo) AS Costo, v.CdC
FROM dbo.DM_VociMese AS v INNER JOIN
dbo.DM_VociGruppi AS vg ON v.VRE = vg.VRE AND v.DatCed BETWEEN vg.DatIni AND vg.DatFin LEFT OUTER JOIN
dbo.DM_TabGruppi AS tg ON vg.IDGruppo = tg.IDGruppo
WHERE (1 = 1) AND (tg.CostRelevant = 'X')
GROUP BY v.CodAzi, v.CID, v.Liv, v.VRE, v.DatCed, vg.IDGruppo, v.CoGe, v.CdC
GO
The table DM_VociMese contains +22ml rows, the table DM_VociGruppi contains 287 rows and the table DM_TabGruppi contains 36 rows.
Thank you in advance.
