Join columns side by side in result set

oracle, sql

Solution

SELECT t1.FirstName1, t1.LastName1, t2.FirstName2, t2.LastName2
FROM
    (SELECT 
         FirstName1, 
         LastName1, 
         ROW_NUMBER() OVER (ORDER BY FirstName1) 'RowNumber'
     FROM table1
    ) AS t1
    FULL OUTER JOIN
    (SELECT 
         FirstName2, 
         LastName2, 
         ROW_NUMBER() OVER (ORDER BY FirstName2) 'RowNumber'
     FROM table2
    ) AS t2
    ON t1.RowNumber = t2.RowNumber

`FULL OUTER JOIN` will handle the cases where the number of rows from the two tables are not the same.

Problem

I would like my result to look something like this ``` FirstName1 LastName1 FirstName2 LastName2 Amy Smith Bob Stone Fred Joker Gina White ``` Where FirstName1 and FirstName2 have same data types but nothing I can use to join (assume no one has same names) and the same goes for LastName1 and LastName2. I tried to create 2 tables. First table contains FirstName1 and LastName1. Second table contains Firstname2 and LastName2. Then I use ``` SELECT table1.FirstName1, table1.LastName1, table2.FirstName2, table2.LastName2 FROM table1, table2; ``` But this gives me a lot of duplicates. Any suggestions?

Original source