I am not a mysql pro so this is exhasting but helped me learn a lot in the process as well.
here's a sql EXAMPLE that I made with my own DB to demostrate the concept.
i hope this is what you want.
the core concept is to generate a auto_increment column for each table/query results, and then left / right join depends on which table have more rows.
to make a auto increamenting column within 1 SQL is fiddly, what i did is initiate the @cnt variable by
(select @cnt := 0 AS rowNumber) as t0
but i never used that table. I then do
select (@cnt := @cnt + 1) AS rowNumber, ....
in the following table for the incrmental column.
the method have a critical weakness which is you have to know which column/query will return the most row and predefine the type of JOIN in the SQL. I am not sure if this helps you in any way but I'd like to share it non the less.
mysql> select t1.rowNumber,t1.friend, t2.rowNumber,t2.avatar from (select @cnt := 0 AS rowNumber) as t0 RIGHT JOIN (select (@cnt := @cnt + 1) AS rowNumber,friend from friends) as t1 using (rowNumber) LEFT JOIN (select @cnt := 0 AS rowNumber) as t4 using (rowNumber) RIGHT JOIN (select (@cnt := @cnt + 1) AS rowNumber,avatar from user) as t2 using (rowNumber);
+-----------+-------------+-----------+------------+
| rowNumber | friend | rowNumber | avatar |
+-----------+-------------+-----------+------------+
| 1 | 0001 | 1 | 0001 |
| 2 | 0002 | 2 | 0002 |
| 3 | 0003 | 3 | 0003 |
| 4 | 0004 | 4 | 0004 |
| 5 | 0005 | 5 | 0005 |
| 6 | 0006 | 6 | 0006 |
| 7 | 0007 | 7 | 0007 |
| 8 | 0008 | 8 | 0008 |
| 9 | 0009 | 9 | 0009 |
| 10 | 0010 | 10 | 0010 |
| NULL | NULL | 11 | 0011 |
| NULL | NULL | 12 | 0012 |
| NULL | NULL | 13 | 0013 |
| NULL | NULL | 14 | 0014 |
| NULL | NULL | 15 | 0015 |
| NULL | NULL | 16 | 0016 |
| NULL | NULL | 17 | 0017 |
| NULL | NULL | 18 | 0018 |
| NULL | NULL | 19 | 0019 |
| NULL | NULL | 20 | 0020 |
+-----------+-------------+-----------+------------+
20 rows in set (0.00 sec)
