Question:

All documentation I’ve read suggests that a UNION will automatically eliminate duplicates.  But when I run this query I get duplicates:

select
universe.*,
usystems.*

from
universe

left join usystems
on
universe.ipcapturedate = usystems.Acapturedate
and
universe.ipdin = usystems.Adin

UNION

select
universe.*,
usystems.*

from
universe

left join usystems
on
universe.ipcapturedate = usystems.Bcapturedate
and
universe.ipdin = usystems.Bdin
_________________________________________________________

I’ve resorted to running this query, but there must be something inefficient (or flat out incorrect)  about it because it’s running for ever.  Granted, “Universe” contains 800,000 rows.  Usystems, however, is not very large.

select
universe.*,
usystems.*

from
universe

left join usystems
on
(universe.ipcapturedate = usystems.Acapturedate
and
universe.ipdin = usystems.ADIN)

or
(universe.ipcapturedate = usystems.Bcapturedate
and
universe.ipdin = usystems.BDIN)

Thanks!

Solution:

Without sample data it would be hard to say what is going on with the union but it is possible that what appear to be duplicates are actually unique in some way not visible.

In any case your second query is, in my eyes a lot more readable and, almost certainly more efficient than the union.