SQL UNION - illogical results

Question

I have an interesting problem with UNION in SQL.

My Statement is of this form:

with tab as (
(select FldA, FldB From  Table1A inner join Table1B on Field1A=Field1B)
UNION
(select FldA, FldB From  Table2A inner join Table2B on Field2A=Field2B)
)
select * from tab
where FldA="XYZ"

When I run this, I get only 1 row returned - which is not correct. I can verify it's not correct just by commenting the UNION and second Select statement:

with tab as (
(select FldA, FldB From  Table1A inner join Table1B on Field1A=Field1B)
--UNION
--(select FldA, FldB From  Table2A inner join Table2B on Field2A=Field2B)
)
select * from tab
where FldA="XYZ"

If I run this version, I get THREE rows returned! I assume I'm doing something stupid - but I can't imagine what.

Answer 1

UNION removes duplicates, to keep duplicates use UNION ALL.

In this sense, UNION works the same as DISTINCT. As with DISTINCT records are considered duplicates if the records are identical for every (selected) column.

UNION removes duplicates regardless of in which set the duplicates occur, since the DISTINCT is executed after the UNION.

If you have 2 queries/data sets A and B, if you UNION these together, you get the DISTINCT combination of both. If there are duplicates in A they are removed. If a record exists in A and in B, it is also returned uniquely (i.e., it only occurs once in your final result set).