Joining columns in SQL

Consider the following SQL table:

id | col1 | col2
----------------
1  |  a   |  b
2  |  c   |  d

What query would get me the distinct result set of a,b,c,d? I know I can do:

SELECT distinct col1 FROM `table` UNION
SELECT distinct col2 FROM `table`

but that seems ugly (and doesn't get me the entire 开发者_如何学运维distinct set); plus, I'm not sure how I can do further processing with that result set. Any better idea?

Beauty is in the eye of the beholder. UNION is one of the five primitive operators of Codd's relational algebra and for me it is a thing of beauty.

Your SQL code should indeed get you the entire distinct result set; In fact, you should be able to omit the DISTINCT keywords and the code should still get you the entire distinct result set (hint: a set does not contain duplicate members).

Perhaps not better, but different:

select distinct sub.val from (
  select col1 as val from table
  union all
  select col2 as val from table
) as sub

You could probably use LAG and LEAD functions to get the values of the previous and next row in a single result set. Check out this article:

http://explainextended.com/2009/03/10/analytic-functions-first_value-last_value-lead-lag/