Search code examples
mysqlsqlselectdistinct

MySQL: SELCT DISTINCT on two columns into one alias column

Let's admit I have this table

+---------+---------+
| columnA | columnB |
+---------+---------+
|       1 |       2 |
|    NULL |       3 |
|       5 |       4 |
|       1 |       5 |
|       1 |       6 |
|       2 |    NULL |
|       2 |       3 |
|       6 |       4 |
|       2 |       5 |
+---------+---------+

And I want to do something like this (to select the distinct values of two columns into a alias column):

SELECT DISTINCT (columnA, columnB) AS columnC

To get this output

+---------+
| columnC |
+---------+
|       1 |
|       2 |
|       3 |
|       4 |
|       5 |
|       6 |
+---------+
Solution

  • You could union queries on both columns, which (implicitly) returns the distinct values from both columns:

    SELECT columnA AS columnC FROM mytable WHERE columnA IS NOT NULL 
UNION
SELECT columnB AS columnC FROM mytable WHERE columnB IS NOT NULL 
ORDER BY columnC;