mysql UNION result has less rows than sum of table row counts

Question

I'm using UNION to get all names in different tables.
my tables has about 10000 rows all together.
but the query returns 468 rows!!
My query is:

SELECT name FROM `shopping` 
UNION 
SELECT name FROM stores 
UNION 
SELECT name FROM concert 
UNION 
SELECT val AS name FROM event 
UNION 
SELECT name FROM fastfood

Where is the problem?

score 31 · Accepted Answer · answered Sep 24 '12 at 21:27

31

UNION removes duplicate values. You probably want UNION ALL instead.

answered Sep 24 '12 at 21:27

Joe Stefanelli

132,803
19
237
235

mysql UNION result has less rows than sum of table row counts

1 Answers1