UNION ALL on same table

This topic is locked

UNION ALL on same table	2/22/2014 12:06:29 AM
PHPRunner General questions
F F5447 author Dear All, I read from the Internet saying that MySQL UNION ALL is faster than UNION, and I have a MyDraw table as follows: `DRAWNO PRIZENO FIRSTD SECONDD THIRDD FOURTHD M123/14 8458 8 4 5 8 M123/14 7214 7 2 1 4 M123/14 1640 1 6 4 0 M123/14 2103 2 1 0 3 M123/14 2557 2 5 5 7 M123/14 2975 2 9 7 5 M123/14 3767 3 7 6 7 M123/14 4409 4 4 0 9 M123/14 4887 4 8 8 7 M123/14 5844 5 8 4 4` I would like to count and sum them UP how many times of occurrence of digit from 0 to 9 (0,1,2,3,4,5,6,7,8,9) for 4 different differenct columns, which is FIRSTD, SECONDD, THIRDD, FORTHD ... Expected result is ... `DRAWNO [D0][D1][D2][D3][D4][D5][D6][D7][D8][D9] M123/14 3 3 4 2 8 5 2 6 5 2` I have posted question to stackexchange, as follows: [url]http://dba.stackexchange.com/questions/57539/mysql-count-multiple-columns-and-sum-the-total-occurrence[/url] Solution given by using UNION, which works fine; but one of the expert suggest to use UNION ALL. I tried to figure out how, and read MySQL from the Internet, it seems that all example given is UNION ALL for different tables; but for my case, it is same table. I would like to use UNION ALL, if possible as it is faster, I assume. Thanks in advance.


	Sergey Kornilov admin 2/22/2014
	UNION and UNION ALL can be applied to SQL statements pulling data from the same table or from different tables. The only difference is that UNION removes duplicate records and therefor slower. More info: http://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all If you do not expect any duplicate records use UNION ALL