how to select unmached rows

Question

create table user_info(
    userID int,
    names varchar(20),
    fatherName varchar(20),
    grade varchar(10),
    status varchar(20)
)

insert into user_info values(001,'raj singh','venkatesh','A','active')
insert into user_info values(008,'atal bihari','ram pratap','c','departed') 
insert into user_info values(005,'sultaan mirja','jai khan','b','active')
insert into user_info values(009,'faiz ali','mahboob ali','d','termineted')
insert into user_info values(003,'david sam','david rk','A','awarded')

userID  names           fatherName     grade   status
----------------------------------------------------
001     raj singh       venkatesh      A       active
008     atal bihari     ram pratap     c       departed
005     sultaan mirja   jai khan       b       active 
009     faiz ali        mahboob ali    d       termineted 
003     david sam       david rk       A       awarded 

The second table:

create table user_contact(
    userID int,
    contectNo int,
    houseNo varchar(20),
    city varchar(20),
    grade varchar(10)
)

insert into user_contact values(001,'2586931475','384 b block','ludhiyan','A')
insert into user_contact values(005,'9817588937','786 sector 4','lahore','b')
insert into user_contact values(0010,'8081829973','164 p.road','kanpur','A')
insert into user_contact values(0011,'7898654532','132 ram janki puram','ayodhya','c')
insert into user_contact values(009,'8978231254','420 coco-cola','limka','e')

userID    contectNo      houseNo               city            grade  
--------------------------------------------------------------------
001       2586931475     384 b block           ludhiyan        A 
005       9817588937     786 sector 4          lahore          b 
0010      8081829973     164 p.road            kanpur          A 
0011      7898654532     132 ram janki puram   ayodhya         c 
009       8978231254     420 coco-cola         limka           e 

I want to result:

names           fatherName          contectNo             city 
------------------------------------------------------------------
atal bihari     ram pratap          null                  null
david sam       david rk            null                  null        
null            null                8081829973            kanpur  
null            null                7898654532            ayodhya

Answer 1

Use FULL OUTER JOIN

SELECT ui.names,
       ui.fathername,
       uc.contectNo,
       uc.city
FROM   user_contact uc
       FULL OUTER JOIN user_info ui
                    ON uc.userID = ui.userID
WHERE  ui.userID IS NULL
        OR uc.userID IS NULL