CODEWITHSUNDEEP
postgresql
Dhairya Lakhera
Dhairya Lakhera

Reputation: 4808

postgres find matched and unmatched records

I have two table

offline_tbl

cen | subcen
------------
01  | 002 
------------
02  | 001 
------------
02  | 003 
------------
03  | 001 
------------
03  | 009 
------------

online_tbl

cen | subcen
------------
01  | 002 
------------
02  | 001 
------------
02  | 004
------------
03  | 001 
------------
03  | 008
------------
04  | 079
------------
05  | 006
------------

I have two Table offline_tbl and online_tbl. I would like to match offline_tbl cen and subcen with online_tbl cen and subcen.

records which are either present in offline_tbl or online_tbl , but not in both tables.

my expected output

cen | subcen
------------
02  | 003 
------------
02  | 004 
------------
03  | 008
------------
03  | 009 
------------
04  | 079
------------
05  | 006
------------

Upvotes: 0

Views: 889

Answers (1)

klin
klin

Reputation: 121574

Use full join:

select 
    a.cen as offline_cen, 
    a.subcen as offline_subcen, 
    b.cen as online_cen, 
    b.subcen as online_subcen
from offline_tbl a
full join online_tbl b 
on a.cen = b.cen and a.subcen = b.subcen

 offline_cen | offline_subcen | online_cen | online_subcen 
-------------+----------------+------------+---------------
 01          | 002            | 01         | 002
 02          | 001            | 02         | 001
 02          | 003            |            | 
             |                | 02         | 004
 03          | 001            | 03         | 001
             |                | 03         | 008
 03          | 009            |            | 
             |                | 04         | 079
             |                | 05         | 006
(9 rows)

If you want to get only values present in one of the tables:

select 
    coalesce(a.cen, b.cen) as cen, 
    coalesce(a.subcen, b.subcen) as subcen
from offline_tbl a
full join online_tbl b 
on a.cen = b.cen and a.subcen = b.subcen
where a.cen is null or b.cen is null

 cen | subcen 
-----+--------
 02  | 003
 02  | 004
 03  | 008
 03  | 009
 04  | 079
 05  | 006
(6 rows)

Upvotes: 1

Related Questions