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

I don't think you can avoid a temporary table in this case... — jishi, Mar 29 '11 at 14:11
is this is for presentation, or is there any kind of relation between the data in the various tables? If it's for presentation, you're better off doing one query for each table and then processing the results in your application layer. — dnagirl, Mar 29 '11 at 14:12
@jishi Thanks for the comment. Do you have any example with temporary tables? I do not want 1000 lines of code. Maybe I am missing some basic syntax :-) — eisberg, Mar 29 '11 at 14:12
@dnagirl Long story short. There is no application layer. It is some kind of admin only view. — eisberg, Mar 29 '11 at 14:13
If it's just for presentation, I would go with dnagirls suggestion. — jishi, Mar 29 '11 at 14:14
then as @Smandoli says, a stored procedure is probably the way to go. You'd create a cursor for each table and loop through them concurrently. — dnagirl, Mar 29 '11 at 14:16

Cade Roux · Accepted Answer · 2011-03-29T14:26:16.943

5

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

edited Mar 29 '11 at 14:26

answered Mar 29 '11 at 14:16

Cade Roux

88,164
40
182
265

@Cade Roux Thank you for the reply. There are very easy workarounds for ROW_NUMBER() but as far as I know there are no workarounds for FULL OUTER JOINs :-( – eisberg Mar 29 '11 at 14:20
@eisberg Another reason I don't use MySQL! (although I only have to use a FULL OUTER JOIN once a year) - FULL OUTER JOIN is easy to workaround (and more elegant) in this case. You have your standard "Numbers" table, right? – Cade Roux Mar 29 '11 at 14:24
@Cade Roux Lets not start a MySQL vs anything - thing :-) The tables are far more complex than I can show here but yes all can be sorted in certain ways and need to be compared in a report. – eisberg Mar 29 '11 at 14:26
@eisberg - I edited answer to use a standard Numbers table. I usually have mine populated to 1 million rows. Although you may want to do it in a stored procedure, I still thuink you can do it without cursors and temporary tables. Also see this: http://stackoverflow.com/questions/1895110/row-number-in-mysql and this http://www.artfulsoftware.com/infotree/queries.php?&bw=1440#1098 – Cade Roux Mar 29 '11 at 14:34
@Cade Roux Thank you again :-) A numbers table is quite a nice way but since I have to deal with an unknown number of rows (maybe > 100,000,000) this will become funny. I will definitely try this later. – eisberg Mar 29 '11 at 16:53
@Cade Roux I do not like the extra table, because it is not "portable" from one server to another without copying. But it works fine with the right index keys and is fast as lightning. Thank you. Accepted. – eisberg Mar 31 '11 at 07:33
@eisberg I understand being reluctant to introduce something special, but every server just needs one Numbers table in a public database. Even without it, there are ways to fake one with a cross join of system metadata tables. – Cade Roux Mar 31 '11 at 14:03

score 0 · Answer 2 · answered Mar 29 '11 at 15:59

This is a tricky method using prepared queries:

SET @r1= (SELECT COUNT(*) FROM table1);
SET @r2= (SELECT COUNT(*) FROM table2);
SET @a=-1;

SET @query= IF (@r1 > @r2, 
    (
        SELECT GROUP_CONCAT(CONCAT(
        'SELECT (SELECT col1 FROM table1 LIMIT ', @a:=@a+1, ', 1) AS col1,
        (SELECT col1 FROM table2 LIMIT ', @a, ', 1) AS col2 FROM table1')
        SEPARATOR ' union ') FROM table1
    ),
    (
        SELECT GROUP_CONCAT(CONCAT(
        'SELECT (select col1 from table1 limit ', @a:=@a+1,', 1) as col1,
        (SELECT col1 FROM table2 LIMIT ', @a, ', 1) AS col2 FROM table2')
        SEPARATOR ' union ') FROM table2
    )
);
PREPARE my_query FROM @query;
EXECUTE my_query;

Mind it's not an effective method at all, it just solves your problem.

I tried this and it is just to slow and will get messy with more and more tables. But none the less thank you! — eisberg, Mar 29 '11 at 16:57

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

2 Answers2