How to do a "horizontal UNION" in MySQL? (concating tables)

Question

I am looking for a possibility to concat or union two (or more) tables. I cannot JOIN all tables as there is no reference an no matching columns. If I join without any "ON" I will get all data from Table 1 multiplied with all data from Table 2 (not what I want). So I tried to provide fake rownums with MySQL user defined variables but as there are different amounts of data in all tables I cannot compare those. This is a little hard to explain. So I will provide some examples.

Example 1 (Table 1 = 4 Rows, Table 2 = 3 Rows, Result = 4 Rows)

+---------+---------+---------------+
| Table 1 | Table 2 | Result        |
+---------+---------+-------+-------+
| Col 1   | Col 1   | Col 1 | Col 2 |
+---------+---------+-------+-------+
| A       | H       | A     | H     |
| B       | I       | B     | I     |
| C       | J       | C     | J     |
| D       |         | D     | NULL  |
+---------+---------+-------+-------+

Example 2 (Table 1 = 3 Rows, Table 2 = 4 Rows, Result = 4 Rows)

+---------+---------+---------------+
| Table 1 | Table 2 | Result        |
+---------+---------+-------+-------+
| Col 1   | Col 1   | Col 1 | Col 2 |
+---------+---------+-------+-------+
| A       | H       | A     | H     |
| B       | I       | B     | I     |
| C       | J       | C     | J     |
|         | K       | NULL  | K     |
+---------+---------+-------+-------+

Example 3 (Table 1 = 3 Rows, Table 2 = 4 Rows, Table 3 = 2 Rows, Result = 4 Rows)

+---------+---------+---------+-----------------------+
| Table 1 | Table 2 | Table 3 | Result                |
+---------+---------+---------+-------+-------+-------+
| Col 1   | Col 1   | Col 1   | Col 1 | Col 2 | Col 3 |
+---------+---------+---------+-------+-------+-------+
| A       | H       | O       | A     | H     | O     |
| B       | I       | P       | B     | I     | P     |
| C       | J       |         | C     | J     | NULL  |
|         | K       |         | NULL  | K     | NULL  |
+---------+---------+---------+-------+-------+-------+

Now for the real hard part. I need this as a query. I do not want to fill other temporary tables. If possible :-)

Answer 1

I take it you are joining on the dense ordinal rank of each table simply ordered by the "Col 1" value?

This is effectively a full outer join by the rank of each table.

Unfortunately MySQL doesn't support the ROW_NUMBER() analytical function, which would make this relatively simple:

SELECT *
FROM (SELECT "Col 1", ROW_NUMBER() (OVER ORDER BY "Col 1") AS RowNum FROM "Table 1") AS T1
FULL OUTER JOIN (SELECT "Col 1", ROW_NUMBER() (OVER ORDER BY "Col 1") AS RowNum FROM "Table 2") AS T2
    ON T2.RowNum = T1.RowNum
FULL OUTER JOIN (SELECT "Col 1", ROW_NUMBER() (OVER ORDER BY "Col 1") AS RowNum FROM "Table 3") AS T3
    ON T3.RowNum = T2.RowNum
ORDER BY COALESCE(T1.RowNum, T2.RowNum, T3.RowNum)

There are alternatives in MySQL, but I'm not familiar with the workarounds.

As far as the FULL OUTER JOIN workaround, it's easy for ranks since the known ranks are simply the natural numbers:

SELECT *
FROM Numbers
LEFT JOIN (SELECT "Col 1", ROW_NUMBER() (OVER ORDER BY "Col 1") AS RowNum FROM "Table 1") AS T1
    ON T1.RowNum = Numbers.Number
LEFT JOIN (SELECT "Col 1", ROW_NUMBER() (OVER ORDER BY "Col 1") AS RowNum FROM "Table 2") AS T2
    ON T2.RowNum = Numbers.Number
LEFT JOIN (SELECT "Col 1", ROW_NUMBER() (OVER ORDER BY "Col 1") AS RowNum FROM "Table 3") AS T3
    ON T2.RowNum = Numbers.Number
WHERE COALESCE(T1.RowNum, T2.RowNum, T3.RowNum) IS NOT NULL
ORDER BY Numbers.Number