Union two query result column-wise

Question

Say if I have two queries returning two tables with the same number of rows. For example, if query 1 returns

| a | b | c |
| 1 | 2 | 3 |
| 4 | 5 | 6 |

and query 2 returns

| d  | e  | f  |
| 7  | 8  | 9  |
| 10 | 11 | 12 |

How to obtain the following, assuming both queries are opaque

| a | b | c | d  | e  | f  |
| 1 | 2 | 3 | 7  | 8  | 9  |
| 4 | 5 | 6 | 10 | 11 | 12 |

My current solution is to add to each query a row number column and inner join them on this column.

SELECT
    q1_with_rownum.*,
    q2_with_rownum.*
FROM (
    SELECT ROW_NUMBER() OVER () AS q1_rownum, q1.*
    FROM (.......) q1
) q1_with_rownum
INNER JOIN (
    SELECT ROW_NUMBER() OVER () AS q2_rownum, q2.*
    FROM (.......) q2
) q2_with_rownum
ON q1_rownum = q2_rownum

However, if there is a column named q1_rownum in either of the query, the above will break. It is not possible for me to look into q1 or q2; the only information available is that they are both valid SQL queries and do not contain columns with same names. Are there any SQL construct similar to UNION but for columns instead of rows?

score 2 · Accepted Answer · answered Mar 27 '21 at 17:48

There is no such function. A row in a table is an entity.

If you are constructing generic code to run on any tables, you can try using less common values, such as "an unusual query rownum" -- or something more esoteric than that. I would suggest using the same name in both tables and then using using clause for the join.

Laurelg · Answer 2 · 2021-03-27T18:22:44.640

1

Not sure if I understood your exact problem, but I think you mean both q1 and q2 are joined on a column with the same name?

You should add each table name before the column to distinguish which column is referenced: "table1"."similarColumnName" = "table2"."similarColumnName"

EDIT: So, problem is that if there is already a column with the same alias as your ROW_NUMBER(), the JOIN cannot be made because you have an ambiguous column name.

The easier solution if you cannot know your incoming table's columns is to make a solid alias, for example _query_join_row_number

EDIT2: You could look into prefixing all columns with their original table's name, thus removing any conflict (you get q1_with_rows.rows and conflict column is q1_with_rows.q1.rows) an example stack on this: In a join, how to prefix all column names with the table it came from

edited Mar 27 '21 at 18:22

answered Mar 27 '21 at 17:48

Laurelg

11
3

Also, make sure your rows are matching where you want with an ORDER BY, if there is no id to either query. If there is meaning to each row like "client1" and "client2" who should have their own results to column "a" and "f" not mixed up – Laurelg Mar 27 '21 at 17:50
I am not able to look into either of the query because they are user-inputted SQL strings, so I don't know what columns "table1" actually has... If "table1" already have a column called "rownum" it will clash with my `SELECT ROW_NUMBER() OVER () AS rownum` – user12986714 Mar 27 '21 at 17:54
Cannot believe I forgot, but Gordon Linoff hit it right. `USING (column)` is a shortcut for `ON table1.column = table2.column`. – Laurelg Mar 27 '21 at 17:56
Ah, your question is not related to the JOIN then, but whether the alias can be used. I think the default name generated is `row_number` anyway, just use that? – Laurelg Mar 27 '21 at 18:00
Basically, I have two tables with equal number of rows (and all distinct column names). I want to make a third table containing columns from both tables. It is not possible for me to know what columns each table actually have so no `SELECT q1.col_1, q1.col_2` etc. – user12986714 Mar 27 '21 at 18:04
other than constructing a solid alias to avoid ambiguous conflict, I don't think there is any way to make a "use first column" in SQL. something like query_join_row_number should be solid enough, no? – Laurelg Mar 27 '21 at 18:05

Union two query result column-wise

2 Answers2