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/