Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > CREATE TABLE Table1 ( > FirstName VARCHAR(255), > LastName VARCHAR(255), > State VARCHAR(255), > DOB DATE, > Age NUMBER(5) > ) > ; > > <pre> > ✓ > </pre> <!-- --> > CREATE TABLE Table2 ( > FirstName VARCHAR(255), > LastName VARCHAR(255), > State VARCHAR(255), > Gender VARCHAR(255) > ) > ; > > <pre> > ✓ > </pre> <!-- --> > INSERT ALL > INTO Table1 VALUES ('Mike', 'Holm', 'MN', TO_DATE('1990-01-01', 'YYYY-MM-DD'), 29) > INTO Table1 VALUES ('Kerry', 'Nelson', 'TN', TO_DATE('1991-04-15', 'YYYY-MM-DD'), 28) > INTO Table1 VALUES ('Monica', 'Perkins', 'NY', TO_DATE('1990-12-01', 'YYYY-MM-DD'), 28) > SELECT 1 FROM DUAL; > > <pre> 3 rows affected > </pre> <!-- --> > INSERT ALL > INTO Table2 VALUES ('Mike', 'Holm', 'MN', 'Male') > INTO Table2 VALUES ('Jordan', 'Kohl', 'UT', 'Male') > INTO Table2 VALUES ('Monica', 'Perkins', 'NY', 'Female') > SELECT 1 FROM DUAL; > > <pre> 3 rows affected > </pre> <!-- --> > SELECT t1.FirstName, > t1.LastName, > t1.State, > TO_CHAR(t1.DOB, 'YYYY-MM-DD') AS DOB, > TO_CHAR(t1.Age) AS Age, > t2.Gender > FROM Table1 t1 > INNER JOIN Table2 t2 > ON t1.FirstName = t2.FirstName > AND t1.LastName = t2.LastName > AND t1.State = t2.State > UNION > SELECT t1.FirstName, > t1.LastName, > t1.State, > TO_CHAR(t1.DOB, 'YYYY-MM-DD') AS DOB, > TO_CHAR(t1.Age) AS Age, > 'NA' AS Gender > FROM Table1 t1 > WHERE NOT EXISTS ( > SELECT * > FROM Table2 t2 > WHERE t1.FirstName = t2.FirstName > AND t1.LastName = t2.LastName > AND t1.State = t2.State ) > UNION > SELECT t2.FirstName, > t2.LastName, > t2.State, > 'NA' DOB, > 'NA' AS Age, > T2.Gender > FROM Table2 t2 > WHERE NOT EXISTS ( > SELECT * > FROM Table1 t1 > WHERE t1.FirstName = t2.FirstName > AND t1.LastName = t2.LastName > AND t1.State = t2.State ) > ; > > <pre> > FIRSTNAME | LASTNAME | STATE | DOB | AGE | GENDER > :-------- | :------- | :---- | :--------- | :-- | :----- > Jordan | Kohl | UT | NA | NA | Male > Kerry | Nelson | TN | 1991-04-15 | 28 | NA > Mike | Holm | MN | 1990-01-01 | 29 | Male > Monica | Perkins | NY | 1990-12-01 | 28 | Female > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=8d6cac6bd9108894b37ce7e43490bed8)*
back to fiddle