Duplicates in Union Query

[Guest]

I have a quick question. . . If I have duplicates in Table A and I run a
union query between tables A and B, will it eliminate the duplicates in Table
A, or do I have to use a "SELECT DISTINCT" when selecting table A?

 

[John Spencer]

If you use UNION in the UNION query, then DISTINCT is applied against
all the records returned by the various queries in the UNION.

If you use UNION ALL then no DISTINCT is applied. If you want to
eliminate Duplicates in table A and not in table B then you would use
UNION ALL and apply DISTINCT against the records in table A

'Distinct from A and all from B
SELECT DISTINCT Field2, Field3
FROM A
UNION ALL
SELECT FieldA, FieldB
FROM B

'Distinct over all records returned
SELECT Field2, Field3
FROM A
UNION
SELECT FieldA, FieldB
FROM B

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Marshall Barton]

I have a quick question. . . If I have duplicates in Table A and I run a
union query between tables A and B, will it eliminate the duplicates in Table
A, or do I have to use a "SELECT DISTINCT" when selecting table A?

A UNION query effectively does a DISTINCT across all the
records in its result set. If you want any duplicate
records in the result set, then use UNION ALL