clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335821 fiddles created (27456 in the last week).

CREATE TABLE Table1 ( FirstName VARCHAR(255), LastName VARCHAR(255), State VARCHAR(255), DOB DATE, Age NUMBER(5) ) ;
 hidden batch(es)


CREATE TABLE Table2 ( FirstName VARCHAR(255), LastName VARCHAR(255), State VARCHAR(255), Gender VARCHAR(255) ) ;
 hidden batch(es)


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;
3 rows affected
 hidden batch(es)


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;
3 rows affected
 hidden batch(es)


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 ) ;
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
 hidden batch(es)