MySQL: Get unique values across multiple columns in alphabetical order

If my table looks like this:

id | colA   | colB | colC
===========================
1  | red    | blue | yellow
2  | orange | red  | red
3  | orange | blue | cyan

What SELECT query do I run such that the results retu开发者_运维百科rned are:

blue, cyan, orange, red, yellow

Basically, I want to extract a collective list of distinct values across multiple columns and return them in alphabetical order.

I am not concerned with performance optimization, because the results are being parsed to an XML file that will serve as a cache (database is hardly updated). So even a dirty solution would be fine.

Thanks for any help!

(SELECT DISTINCT colA AS color FROM table) UNION
(SELECT DISTINCT colB AS color FROM table) UNION
(SELECT DISTINCT colC AS color FROM table)
ORDER BY color

Just do it the normal way:

create table new_tbl(col varchar(50));


insert into new_tbl(col)
select cola from tbl
union
select colb from tbl
union
select colc from tbl

Then sort:

select col from new_tbl order by col

Or if you don't want staging table, just do:

select cola as col from tbl
union
select colb from tbl
union
select colc from tbl
order by col

Note: UNION automatically remove all duplicates, if you want to include duplicates, change the UNION to UNION ALL