sql to combine two unrelated tables into one

Question

I have tables

table1

col1    col2    
a       b
c       d

and table2

mycol1  mycol2
e           f
g           h
i           j
k           l

I want to combine the two tables, which have no common field into one table looking like:

table 3

col1    col2    mycol1  mycol2
a           b   e   f
c           d   g   h
null    null    i   j
null    null    k   l

ie, it is like putting the two tables side by side.

I'm stuck! Please help!

which RDBMS? you clearly don't want across join, but how do you expect to line up the rows. i.e. why does e,f go with a,b ? — Randy, Jul 18 '13 at 14:19
Usually when the question is something like this, you're doing something that doesn't actually make sense. What use do you have for the side-by-side tables? If you're just doing that to display data in your application, there's probably a much better way than what you propose. — catfood, Jul 18 '13 at 14:22
sqlserver. Hi, e, f doesn't need to go with a, b. The pairs of columns in table 3 are independent of each other. — user4109, Jul 18 '13 at 14:27
the resultant table is to be used by someone using sharepoint. It is much easier to give them a single table to play with - that's what I've been asked to provide, even if it doesn't make sense! — user4109, Jul 18 '13 at 14:29
Some just asked me to make a report that works like this, and although its easy to say 'sql doesn't work like that', its quite hard to explain why this is so difficult. I bet this crops up a lot. — codeulike, Sep 24 '18 at 14:02

score 17 · Accepted Answer · answered Jul 18 '13 at 14:22

17

Get a row number for each row in each table, then do a full join using those row numbers:

WITH CTE1 AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY col1) AS ROWNUM, * FROM Table1
),
CTE2 AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY mycol1) AS ROWNUM, * FROM Table2
)
SELECT col1, col2, mycol1, mycol2
FROM CTE1 FULL JOIN CTE2 ON CTE1.ROWNUM = CTE2.ROWNUM

This is assuming SQL Server >= 2005.

answered Jul 18 '13 at 14:22

zimdanen

5,508
7
44
89

good - except this is RDBM specific - others might use ROWNUM – Randy Jul 18 '13 at 14:23
@Randy: Thanks; I added the RDBMS caveat. – zimdanen Jul 18 '13 at 14:24
This works great, irrespective of which table is longer, thanks. Problem solved – user4109 Jul 19 '13 at 08:51
@user4109: Glad to have been of help, and welcome to StackOverflow. When you get a chance, please mark the answer that helped you most as the answer. – zimdanen Jul 19 '13 at 12:54
A warning with this: If you're doing more than two columns you may end up with an excess of rows (i.e. all columns might have at least one NULL in them). The workaround I've found is do the final outer SELECT from the table which has the most elements in it and join the shorter lists, which is not an ideal solution as you don't always know which list is the longest. – Techrocket9 Jan 03 '18 at 23:15

score 2 · Answer 2 · answered Jul 18 '13 at 14:29

2

It's really good if you put in a description of why this problem needs to be solved. I'm guessing it is just to practice sql syntax?

Anyway, since the rows don't have anything connecting them, we have to create a connection. I chose the ordering of their values. Also since they have nothing connecting them that also begs the question on why you would want to put them next to each other in the first place.

Here is the complete solution: http://sqlfiddle.com/#!6/67e4c/1

The select code looks like this:

WITH rankedt1 AS
(
  SELECT col1
  ,col2
  ,row_number() OVER (order by col1,col2) AS rn1
  FROM table1
  )
,rankedt2 AS 
(
  SELECT mycol1
  ,mycol2
  ,row_number() OVER (order by mycol1,mycol2) AS rn2
  FROM table2
  )

SELECT
col1,col2,mycol1,mycol2
FROM rankedt1
FULL OUTER JOIN rankedt2
  ON rn1=rn2

answered Jul 18 '13 at 14:29

David Söderlund

978
8
14

1

many thanks for this, and @zimdanen answer, and the sqlfiddle solution. Blimey, you were quick! I've come across this requirement a few times .. it is not practicing sql syntax! The ultimate destination of the table is for use by a 3rd party configuring sharepoint web parts. – user4109 Jul 18 '13 at 14:43
This works great, irrespective of which table is longer, thanks. Problem solved – user4109 Jul 19 '13 at 08:51
I used it when I needed to pull identity index values from a table which correspond to another table through inheritance. there is no other specific data that links the two because the highest table in the hierarchy is inherited by many tables that are not related by specific data in each. – blindguy Apr 24 '16 at 19:19

score 2 · Answer 3 · edited May 23 '17 at 12:25

Option 1: Single Query

You have to join the two tables, and if you want each row in table1 to match to only one row in table2, you have to restrict the join somehow. Calculate row numbers in each table and join on that column. Row numbers are database-specific; here is a solution for mysql:

SELECT
    t1.col1, t1.col2, t2.mycol1, t2.mycol2
FROM
    (SELECT col1, col2, @t1_row := t1_row + 1 AS rownum FROM table1, (SELECT @t1_row := 0) AS r1) AS t1
    LEFT JOIN
    (SELECT mycol1, mycol2, @t2_row := t2_row + 1 AS rownum FROM table2, (SELECT @t2_row := 0) AS r2) AS t2
    ON t1.rownum = t2.rownum;

This assumes table1 is longer than table2; if table2 is longer, either use RIGHT JOIN or switch the order of the t1 and t2 sub-selects. Also note that you can specify the order of each table separately using an ORDER BY clause in the sub-selects.

(See select increment counter in mysql)

Option 2: Post-processing

Consider making two selects, and then concatenating the results with your favorite scripting language. This is a much more reasonable approach.

I'm back looking at your answers! Actually, I won't know if table1 is longer than table 2 or not. The original question was a grossly oversimplified example of the problem In actual fact, there are going to be a dozen tables that are to be combined into one view (ie not a table, but that isn't important). The size of each of the dozen tables won't be known beforehand. The 3rd party gets the view, so Option 2 isn't really feasible. — user4109, Jul 19 '13 at 08:39
@user4109 BTW, if you don't know which is going to be longer, you can do a 'full outer join' in mysql by `UNION` a left join and a right join. — jmilloy, Jul 20 '13 at 14:29

sql to combine two unrelated tables into one

3 Answers3

Linked