How to retrieve distinct values from more than one column?

Question

I have two columns of data. I'd like to run select statement on them that grabs all the distinct pieces of data in these columns 1 time. For instance.

*column 1*   *column 2*
dog          monkey
monkey       elephant
dog          monkey

I wanna be able to returna result set that has dog, monkey and elephant in it and that's all.

Answer 1

You can use two selects:

Select column1 As c From t
Union
Select column2 From t

Union will take care of duplicates:

The default behavior for UNION is that duplicate rows are removed from the result.

Answer 2

What about the following?

SELECT column1 unique_data FROM your_table
UNION
SELECT column2 unique_data FROM your_table;

Test case:

CREATE TABLE your_table (column1 varchar(50), column2 varchar(50));

INSERT INTO your_table VALUES ('dog', 'monkey');
INSERT INTO your_table VALUES ('monkey', 'elephant');
INSERT INTO your_table VALUES ('dog', 'monkey');

Result:

+-------------+
| unique_data |
+-------------+
| dog         |
| monkey      |
| elephant    |
+-------------+
3 rows in set (0.00 sec)

Answer 3

It may be lil dumb: select distinct from (select distinct from first_column union select distinct from second) - not valid sql, just logic

Answer 4

Use UNION, sample:

select 
    column_1 as name 
from Table_1
union
select 
    column_2 as name 
from Table_1

Output: dog elephant monkey