Select all data from two tables with partial common data

Question

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.

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.

Answer 1

What you want is something like:

SELECT a.first_name,
        a.last_name,
        a.state,
        coalesce ( a.dob, 'NA' ) AS dob,
        coalesce ( a.age, 'NA' ) AS age,
        coalesce ( b.gender, 'NA' ) AS gender
    FROM table_a a
    FULL OUTER JOIN table_b b
        ON ( a.first_name = b.first_name
            AND a.last_name = b.last_name
            AND a.state = b.state ) ;

Note that the above assumes that dob and age are both [var]char data. If they aren't then you will need to cast them as such:

SELECT a.first_name,
        a.last_name,
        a.state,
        coalesce ( to_char ( a.dob ), 'NA' ) AS dob,
        coalesce ( to_char ( a.age ), 'NA' ) AS age,
        coalesce ( b.gender, 'NA' ) AS gender
    FROM table_a a
    FULL OUTER JOIN table_b b
        ON ( a.first_name = b.first_name
            AND a.last_name = b.last_name
            AND a.state = b.state ) ;

Answer 2

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.

Answer 3

       Select firstname,
               lastname,
               state,
               nvl(to_char(DOB),'NA') dob,
               nvl(to_char(AGE),'NA') age,
               nvl(gender,'NA') gender
        from
        (
        SELECT
            a.*,
            b.gender
        FROM
            table1 a
            LEFT JOIN table2 b ON ( a.firstname = b.firstname
                                          AND a.lastname = b.lastname
                                          AND a.state = b.state )
        UNION
        SELECT
            firstname,
            lastname,
            state,
            NULL,
            NULL,
            gender
        FROM
            table2
        WHERE
            ( firstname,
              lastname,
              state ) NOT IN (
                SELECT
                    firstname,
                    lastname,
                    state
                FROM
                    table1
            )
        )

    ORDER BY firstname DESC;


or you can skip outer query using nvl function in subquery


SELECT a.firstname,
       a.lastname,
       a.state,
       nvl(to_char(a.dob), 'NA') dob,
       nvl(to_char(a.age), 'NA') age,
       nvl(b.gender, 'NA') gender
FROM table1 a
LEFT JOIN table2 b ON (a.firstname = b.firstname
                       AND a.lastname = b.lastname
                       AND a.state = b.state)
UNION
SELECT firstname,
       lastname,
       state,
       'NA',
       'NA',
       gender
FROM table2
WHERE (firstname,
       lastname,
       state) NOT IN
    (SELECT firstname,
            lastname,
            state
     FROM table1)
ORDER BY firstname DESC;

Answer 4

So, I replicated your table just to try a few commands (I'm new around here, so it was a little challenging!)

I got something close to what you want but there's still some redundant data left. I couldn't figure out what is causing that but I hope you can get something from that.

You can see there's a random row at the end with gender data. Well, here's the code (you may need to adapt to your tables):

select a.firstname, a.lastname, a.state, a.dob, a.age, b.gender from table1 a full outer join table2 b on a.firstname = b.firstname
union
select b.firstname, b.lastname, b.state, a.dob, a.age, b.gender from table2 b full outer join table1 a on a.firstname = b.firstname
where b.firstname in (select firstname from table2 where firstname not in (select firstname from table1))