SQL - Joining two Select results horizontal

Question

I am creating the following two temporary tables t1 and t2 using two SELECT statements:

+------+------+
| Col1 | Col2 |
+------+------+
| A    |    1 |
| B    |    2 |
| C    |    3 |
+------+------+

and

+------+------+
| Col3 | Col4 |
+------+------+
| C    |    5 |
| D    |    6 |
| E    |    7 |
+------+------+

The two SELECT statements by nature are always returning the same number of rows. Now I want to join/combine these two results horizontally to get the following output table:

+------+------+------+------+
| Col1 | Col2 | Col3 | Col4 |
+------+------+------+------+
| A    |    1 | C    |    5 |
| B    |    2 | D    |    6 |
| C    |    3 | E    |    7 |
+------+------+------+------+

I tried working with multiple JOIN statement, but could figure out a smart way. I also tried the UNION statement, which delivered a vertical join, but not the required horizontal version.

Here two easy SELECT statement for better orientation in possible solutions:

SELECT * FROM `t1` WHERE date = DATE(NOW())

SELECT * FROM `t2` WHERE date = DATE(NOW())

Thanks in advance for your help.

Answer 1

Try this

SET @row_number_t1:=0;
SET @row_number_t2:=0;

SELECT t1_modif.*, t2_modif.* FROM 
    (SELECT @row_number_t1:=@row_number_t1+1 AS row_number,
            t1.* FROM t1) 
    t1_modif
JOIN (SELECT @row_number_t2:=@row_number_t2+1 AS row_number,
            t2.* FROM t2) 
    t2_modif ON t2_modif.row_number = t1_modif.row_number

Note that order is not guaranteed, to do this add ORDER BY clause at the end of each FROM t1 and FROM t2 subqueries, basically we are joining by row_number, since MySQL doesn't have ROW_ID, ROW_NUM (similar to mssql, oracle, postgres) we have used session variables