Join two rows Distinctly

Question

If i have a table that looks like

num
1
2
3
4
5
6
7
8
9

And i want to display the same table in two columns

SELECT t1.num, t2.num FROM (SELECT * FROM x) AS t1, (SELECT * FROM x) AS t2

So the result set looks like

num
1,1
2,2
3,3
4,4
5,5
6,6
7,7
8,8
9,9

How would i go about doing this in MySQL

EDIT

Sorry i didnt want to make things complex to start with: But here is what im actually trying to do

To clarify a little more, What im actually trying to do is

num
1,2
2,3
3,4
4,5
5,6
6,7
7,8
8,9

what i want to be able to do further:

SELECT t2.num - t1.num FROM ....

Note that the above query will return all 1s but the values in my database are different to what are displayed above

Thanks in Advance

Answer 1

I don't understand why you don't just do something more simple:

SELECT num AS num1, num AS num2 FROM x

Or if you really want it as a string:

SELECT CONCAT(num, ',', num) AS num FROM x

If you want to select the table twice you could join the table with itself, but I can't see why you would want to do this:

SELECT CONCAT(T1.num, ',', T2.num) AS num
FROM x AS T1
JOIN x AS T2
ON T1.num = T2.num

It would make more sense if you needed fields from different rows, e.g. '1,2', '2,3', etc... Perhaps you have oversimplified things when you made your question? Are you just trying to learn how to use the join syntax?

Answer 2

You can drop everything else, and just list the column twice in the select statement. So in this case, it'd be:

Select x.num as First_Num, x.num as Second_num from x

Answer 3

SELECT
  CONCAT(num, ',', num) AS num
FROM
  x