mysql union doesn't work as expected

Question

I had a mysql query :

Select count(*) as cnt from  page06 where dl = 1 and TYPE = 'mempage' union 
Select count(*) as cnt from  page06 where err = 1 and TYPE = 'mempage' union  
Select count(*) as cnt from  page06 where dl = 1 and err=1 and TYPE = 'mempage'

In most cases dl and err columns are 0 so two last select will return 0. I found out this query returns only one that is not what i expected. I found this solution:

Select count(*) as cnt,'s' as p from  page06 where dl = 1 and TYPE = 'mempage' union 
Select count(*),'d' as p  from  page06 where err = 1 and TYPE = 'mempage' union  
Select count(*),'f' as p   from  page06 where dl = 1 and err=1 and TYPE = 'mempage'

This query works perfectly but I have written codes in first format. I want to know why this happens and is there any solution (Except what I said) to solve this problem?
The reason I don't want to use my solution is I have lots of written queries that I don't want to (or even cannot) change them! what I'm seeking is some king of options to set to connection (like set names 'latin1';)

`UNION` will discard duplicates from the final result. Use `UNION ALL` to be sure all rows are returned. — Michael Berkowski, Jul 17 '12 at 20:42

score 3 · Accepted Answer · edited May 23 '17 at 11:56

3

Use UNION ALL instead of UNION

Check this post for a detailed explanation on the difference : What is the difference between UNION and UNION ALL?

SELECT count(*) AS cnt FROM  page06 WHERE dl = 1 AND TYPE = 'mempage' UNION ALL
SELECT count(*) AS cnt FROM  page06 WHERE err = 1 AND TYPE = 'mempage' UNION ALL
SELECT count(*) AS cnt FROM  page06 WHERE dl = 1 AND err=1 AND TYPE = 'mempage'

edited May 23 '17 at 11:56

Community

1
1

answered Jul 17 '12 at 20:42

Chandu

81,493
19
133
134

Thanks, works perfectly. Sorry to ask this irrelevant question but do you know a good resource for learning mysql? – undone Jul 17 '12 at 20:47

mysql union doesn't work as expected

1 Answers1