MySQL: getting unique values ​​across multiple columns in alphabetical order

Question

If my table looks like this:

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

Which SELECT query I run so that the returned results:

blue, cyan, orange, red, yellow

Basically, I want to extract a collective list of different values ​​from several columns and return them in alphabetical order.

I'm not interested in performance optimization, because the results are parsed to an XML file that will serve as a cache (the database is unlikely to be updated). So even a dirty decision will be fine.

Thanks for any help!

Answer 1

(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

Answer 2

Just do it the usual 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

, , :

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

: UNION , , UNION UNION ALL