What is causing bizarre discrepancy between these 2 Selects?

Hello,

When I issue you this SELECT statement by itself:

SELECT swi.email
FROM swd, swi
WHERE swd.category IN (147)
AND swd.answer = ‘yes’
AND swi.id = swd.ix_id
ORDER BY RAND()
LIMIT 500;

produces 500 results.

But when I issue the same SELECT via a UNION, that is this:

(SELECT email
FROM users
WHERE category IN (147)
AND answer=‘yes’
AND user_status = ‘1’
ORDER BY RAND()
LIMIT 250)
UNION
(SELECT swi.email
FROM swd, swi
WHERE swd.category IN (147)
AND swd.answer = ‘yes’
AND swi.id = swd.ix_id
ORDER BY RAND()
LIMIT 500);

Then I am getting 410 results!
So I should be getting at least 500, which I am getting when 2nd SELECT is issued by itself, however when I put it in this UNION then I am getting
less than what I was getting when the 2nd SELECT was issued by itself!

What is going on???

Regards,

a UNION deletes duplicate rows of the two results. Do you need a UNION ALL which would return all results from both SELECT statements?

guelphdad,

The thing is that in the above example the 1st SELECT returns ZERO results by itself.
2nd SELECT returns the LIMIT of 500 results by itself.
But when you put them in that UNION, then the results returned is 410!
How can that be?

the 2nd subquery returns 500 by itself, but these contain duplicates

subjecting these to the effect of a UNION operation removes the dupes

r937, are you sure about that?

I have not heard that or read that anywhere!
In fact, I thought to get remove duplicate results in a UNION you need to specifically write: UNION DISTINCT

Regards,

well, you could, you know, consult the manual :smiley: :smiley:

if neither DISTINCT nor ALL is declared, DISTINCT is the default