Incorrect Record Count in Union

[Pete]

Hopefully this isn't a duplicate. It's similar to (but not quite the same as)
the question posed by aybs10 on 2/7.

I have a table X with 500,000 rows.

The following query returns 20,000 rows:
SELECT A, B, C, D
FROM X
WHERE len(B) = 9;

The following query returns 480,000 rows:
SELECT A, B, C, D
FROM X
WHERE len(B) = 16;

But the following query returns only 490,000 rows:
SELECT A, B, C, D
FROM X
WHERE len(B) = 9;
UNION
SELECT A, B, C, D
FROM X
WHERE len(B) = 16;

Meanwhile, the following (changing the UNION to a UNION ALL) returns all
500,000:
SELECT A, B, C, D
FROM X
WHERE len(B) = 9;
UNION ALL
SELECT A, B, C, D
FROM X
WHERE len(B) = 16;

The first two queries seem to prove that length of B is always either 9
bytes or 16 bytes. But the latter two queries seem to prove that Access is
seeing 10,000 B's as simultaneously having both length 9 and length 16.

This theory is disproved by the following, which returns zero rows:
SELECT A, B, C, D
FROM X
WHERE len(B) = 9
and len(B) = 16

Anyone have any idea what's happening here?

 

[Tom Ellison]

Dear Pete:

When you say UNION it will eliminate duplicates. Change to UNION ALL and it
will not eliminate duplicates. This is faster, and will cause it to add up
as you expect.

You may want to do some query work to investigate what those duplicates are.

Tom Ellison

 

[Duane Hookom]

A standard UNION query will remove duplicates. UNION ALL will not.

 

[Pete]

Never mind... Sorry to disturb...

There were duplicates amongst the 480,000 that weren't being eliminated
until I did the UNION.

 

[Tom Ellison]

Oh! Really? : )

No kidding! I guess we got this one right!

It's funny, but not at your expense. After all, you figured it out without
help. Just a timing thing. That's a fundamental element to good humor.

Tom Ellison

 

[Marshall Barton]

Hopefully this isn't a duplicate. It's similar to (but not quite the same as)
the question posed by aybs10 on 2/7.

I have a table X with 500,000 rows.

The following query returns 20,000 rows:
SELECT A, B, C, D
FROM X
WHERE len(B) = 9;

The following query returns 480,000 rows:
SELECT A, B, C, D
FROM X
WHERE len(B) = 16;

But the following query returns only 490,000 rows:
SELECT A, B, C, D
FROM X
WHERE len(B) = 9;
UNION
SELECT A, B, C, D
FROM X
WHERE len(B) = 16;

Meanwhile, the following (changing the UNION to a UNION ALL) returns all
500,000:
SELECT A, B, C, D
FROM X
WHERE len(B) = 9;
UNION ALL
SELECT A, B, C, D
FROM X
WHERE len(B) = 16;

The first two queries seem to prove that length of B is always either 9
bytes or 16 bytes. But the latter two queries seem to prove that Access is
seeing 10,000 B's as simultaneously having both length 9 and length 16.

This theory is disproved by the following, which returns zero rows:
SELECT A, B, C, D
FROM X
WHERE len(B) = 9
and len(B) = 16

I think the first UNION query only proves that there are
10,000 rows that match some other record in all four fields.

 

[Pete]

I'll brush up on my discrete mathematics...

Oh! Really? : )

No kidding! I guess we got this one right!

It's funny, but not at your expense. After all, you figured it out without
help. Just a timing thing. That's a fundamental element to good humor.

Tom Ellison