UNION vs UNION ALL

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rachael - 31 Jul 2006 21:56 GMT

I'm using a UNION query to join survey results for different types of
questions (rating questions are in one table, text answers are in a different
table). I ran into a situation where using UNION ALL to get data from the two
tables was only bringing back data from the first table in the query. But,
when I changed it to UNION (*not* UNION ALL), all data were brought back.

I thought that UNION ALL is needed to ensure all data are brought back. Why
would using UNION ALL in my case actually return *less* data?

I can post my query if needs be, but I was wondering if this was more of a
general phenomenon and not necessarily limited to my query.

Thanks
rachael

Reply to this Message

Michel Walsh - 31 Jul 2006 23:18 GMT

Hi,

Should not. UNION ALL should return all the records, while UNION would also
acts like a DISTINCT over the union. As example, a table, with dup, union
with itself will produce less record than there is in the table itself, but
with UNION ALL, it will return twice the number of initial records in the
table.

Hoping it may help
Vanderghast, Access MVP

> I'm using a UNION query to join survey results for different types of
> questions (rating questions are in one table, text answers are in a
[quoted text clipped - 13 lines]
> Thanks
> rachael

Reply to this Message