CODEWITHSUNDEEP
postgresql
nimgwfc
nimgwfc

Reputation: 1509

Get unique values across two columns

I have a table that looks like this:

id | col_1            | col_2 
------+------------------+-------------------
 1 |               12 |                15
 2 |               12 |                16
 3 |               12 |                17
 4 |               13 |                18
 5 |               14 |                18
 6 |               14 |                19
 7 |               15 |                19
 8 |               16 |                20

I know if I do something like this, it will return all unique values from col_1:

select distinct(col_1) from table;

Is there a way I can get the distinct values across two columns? So my output would only be:

12
13
14
15
16
17
18
19
20

That is, it would take the distinct values from col_1 and add them to col_2's distinct values while also removing any values that are in both distinct lists (such as 15 which appears in both col_1 and col_2

Upvotes: 1

Views: 400

Answers (2)

user330315
user330315

Reputation:

You can use a UNION

select col_1
from the_table
union 
select col_2
from the_table;

union implies a distinct operation, the above is the same as:

select distinct col
from (
  select col_1 as col
  from the_table
  union all
  select col_2 as col
  from the_table
) x

Upvotes: 3

Mike Organek
Mike Organek

Reputation: 12484

You will need to use union

select col1 from table
union
select col2 from table;

You will not need distinct here because a union automatically does that for you (as opposed to a union all).

Upvotes: 1

Related Questions