SQL to combine 2 unrelated columns from 2 unrelated tables

Question

I've been looking all over the Internet for a solution to this problem, but nobody seems to have asked this question before.

2 unrelated tables, x columns each. I want a very simple result: to have the 2 columns one next to each other, but with the rows corisponding (row1-row1, row2-row2).

 I will elaborate in case it isn't clear to someone:
 ___________________

 -What I want to do, is to put 2 columns one next to each other:
    -table1.column1
    -table2.column1

  So the result would be something like this:
   table1.column1.ROW1 - table2.column1.ROW1
   table1.column1.ROW2 - table2.column1.ROW2
   table1.column1.ROW3 - table2.column1.ROW3
   table1.column1.ROW4 - table2.column1.ROW4
   table1.column1.ROW5 - table2.column1.ROW5
   ..etc..
 ___________________

A query like "SELECT table1.column1, table2.column1 FROM table1, table2" won't give me the wanted result, as it outputs rows*rows.

IN CASE anyone is wondering why in the world I would need to put 2 unrelated columns together from 2 unrelated tables: My job is to go in a bank, take their databases with clients' information, and mask all the data (in a certain way, not randomly). In the end, I have the original Table of the bank and its perfect clone - the clone Table being populated by the masked data. At that point, the critical part kicks in:

• What I need to do is to compare column by column: 1 column from the original Table of the Bank with its clone - the same identical column, but with the masked data in it instead of the original data (which actually is a whole new column of a whole new Table).

In order to make sure that all the data has been masked properly, respecting all the criteria of the client (the bank), I need to put all the data from column X, right next to all the data from column Y, (row1-row1 ; row2-row2 ; etc).

What I currently do, is to take tens of printscreens and to compare them, which is wasting lots and lots and lots of time.

If anyone could come up with a solution to this problem I would eternally be grateful.

I should also mention that I have nothing to JOIN on, as all the data from table1 is completely different from all the data from table2, nothing in common.

---

THE SOLUTION has been provided by the user 'sstan', and it works perfectly. The query is:

---

select table1.COLUMN, table2.COLUMN

from (select rownum as rn1, table1.COLUMN from YOURTABLE1.COLUMN table1) table1

join (select rownum as rn2, table2.COLUMN from YOURTABLE2.COLUMN table2) table2

on table1.rn1 = table2.rn2;

---

As you adapt this query to your tables, keep in mind that you have to replace just the words I've written in 'CAPITAL' letters.

The query works perfectly on larger datasets as well. I've tested it on 100.000 rows, every row is in his place, the 'rownum' function does not alter them at all.

This is the best and fastest way to put 2 columns one right next to each other, with their rows matched, from 2 different tables (of 2 different users as well in my case), who have absolutely nothing in common to JOIN on.

Answer 1

There has to be something to 'JOIN' on. For example lets assume each row in both tables has a ClientID column

SELECT table1.column1, table2.column1 FROM table1
INNER JOIN table2 ON table2.ClientID = table1.ClientID

Answer 2

Even though we mask the data, there should be some column with data that are common to both the tables. Please have a look for the primary key columns and identity columns for common data - a suggestion.

we can display as per your requirement based on using joins.

Glad to help further

Here is the query. Try something like this

create table table1(column1 varchar(255),column2 varchar(255));
insert into table1(column1,column2)values('a','a1');

--create temp table1
create GLOBAL TEMPORARY TABLE temp1(rownumber int,column1,column2);
insert into temp1 select Row_Number() Over(order by column1),column1,column2 from table1;

--create table2    
create table table2(column1 varchar(255),column2 varchar(255));
insert into table2(column1,column2)values('b','b1');

--create temp table2    
create GLOBAL TEMPORARY TABLE temp2(rownumber int,column1,column2);
insert into temp2 select Row_Number() Over(order by column1),column1,column2 from table1;

--join based on rownumber
select temp1.column1,temp2.column1 from temp1 join temp2 on temp1.rownumber=temp2.rownumber; 

Hope this helps. This should fix the issue.

Answer 3

Unfortunately, what you are asking for is not possible.

Simply doing select * from table1 and select * from table2 side-by-side will not magically pair up the rows the way you are expecting. This is because you can't control how Oracle chooses the store the rows internally, or how it will return them to you (unless you specify an ORDER BY clause). That's not even taking into account that Oracle can move rows around as it sees fit when values are updated.

My recommendation: restart your masking process. It's not too late, right? You still have the original data.

But this time, before you do, add a new column on table1 called Id or something. It doesn't need to have some special value. You can auto-generate it using a sequence. Just make sure each row's Id is unique by creating a unique constraint on it, or better yet, define it as the primary key.

Then, when you mask all the data and place it in table2, make sure to store this Id value in the corresponding row of table2.

Only then will you be able to do what you are asking for with a query such as this:

select t1.*, t2.*
from t1
join t2 on t2.Id = t1.Id

There is no way around this.

EDIT:

What you are asking for is this query:

select t1.*, t2.*
from (select rownum as rn, t.* from table1 t) t1
join (select rownum as rn, t.* from table2 t) t2
  on t2.rn = t1.rn

But, I really don't see how your rows will just magically match the way you expect them too. Hopefully you'll realize that. And if so, I strongly urge you to consider my other option above.

EDIT 2:

I may not be a reliable source of information when it comes to Oracle. But Tom Kyte is. Please read the following thread and his answers. I think this will help you understand the problem better:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6257473400346237629

Answer 4

You can use ROW_NUMBER() OVER (...) OR ROWNUM to generate a pseudo-column to join the tables on. If you want the tables in a specific order for joining then you can specify this using ROW_NUMBER() OVER (ORDER BY column_name).

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE random1 ( ID1, VALUE1 ) AS
          SELECT 1, 20 FROM DUAL
UNION ALL SELECT 5, 32 FROM DUAL
UNION ALL SELECT 3, 5 FROM DUAL;

CREATE TABLE random2 ( ID2, VALUE2 ) AS
          SELECT 6, 17 FROM DUAL
UNION ALL SELECT 4, 10 FROM DUAL
UNION ALL SELECT 2, 7 FROM DUAL;

Query 1:

SELECT ID1,
       ID2,
       VALUE1 - VALUE2 AS difference
FROM   ( SELECT ID1,
                VALUE1,
                ROW_NUMBER() OVER ( ORDER BY NULL ) AS RN
         FROM   RANDOM1 ) a
       INNER JOIN
       ( SELECT ID2,
                VALUE2,
                ROW_NUMBER() OVER ( ORDER BY NULL ) AS RN
         FROM   RANDOM2 ) b
       ON  ( a.RN = b.RN )

Results:

| ID1 | ID2 | DIFFERENCE |
|-----|-----|------------|
|   1 |   6 |          3 |
|   5 |   4 |         22 |
|   3 |   2 |         -2 |

Query 2:

SELECT ID1,
       ID2,
       VALUE1 - VALUE2 AS difference
FROM   ( SELECT ID1,
                VALUE1,
                ROWNUM AS RN
         FROM   RANDOM1 ) a
       INNER JOIN
       ( SELECT ID2,
                VALUE2,
                ROWNUM AS RN
         FROM   RANDOM2 ) b
       ON  ( a.RN = b.RN )

Results:

| ID1 | ID2 | DIFFERENCE |
|-----|-----|------------|
|   1 |   6 |          3 |
|   5 |   4 |         22 |
|   3 |   2 |         -2 |

Answer 5

INNER JOIN both your tables on something like client ID number.