CODEWITHSUNDEEP
mysqlsql
Yuval Adam
Yuval Adam

Reputation: 165242

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?

Upvotes: 4

Views: 242

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

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).

Upvotes: 2

Frank Schmitt
Frank Schmitt

Reputation: 30775

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

Upvotes: 2

maple_shaft
maple_shaft

Reputation: 10463

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/

Upvotes: 0

Related Questions