Select all data from two tables with partial common data

oracle

I have two tables which have 3 same column names and approx half of the records matching.

I want the output table to combine all the rows from both tables whether the rows match or not.

enter image description here

I Tried to run the Union in PL SQL as

SELECT a.First_Name,a.Last_Name, a.State, a.DOB, a.Age, b.Gender
From table1 a, table 2 b
where
a.First_Name || a.Last_Name || a.State = b.First_Name || b.Last_Name || b.State

UNION

SELECT a.First_Name,a.Last_Name, a.State, a.DOB, a.Age, 'NA' Gender
From table1 a
where
a.First_Name || a.Last_Name || a.State != b.First_Name || b.Last_Name || b.State

UNION

SELECT a.First_Name,a.Last_Name, a.State, 'NA' DOB, 'NA' Age, b.Gender
From table2 b
where
a.First_Name || a.Last_Name || a.State != b.First_Name || b.Last_Name || b.State

However, this returns redundant rows and not the expected data.
Could anyone help me with this.

Best Answer

Check out this db<>fiddle for an example. When running your example SELECT statement, it returned an error because the second and third SELECT in the UNIONs reference aliases from the other SELECT statements.

I'm not sure if this is is something you can do in PL/SQL (I have an MSSQL background), but the code in the fiddle returns the expected result set.

Related Question