Number of Rows in a Full Join (DB2 - SQL) -

i running db2/sql - full join query.

    proc sql;     connect db2 db1 (user=&dpwuscod. password=&dpwpwcod.  database=&dpwdbnam.);      create table result3      select *        connection db2       (                select     count(* ) nbrcount                                    full join   b                on         a.pk = b.pk               ,      a.snap_dt='2015-08-31'                ,        a.sys_num not in ('1234')        );     disconnect db2;     quit; 

table has 586,648 rows. table b has 2,384,874 rows.

i expecting number of rows in full join between 2,384,874 (when there complete overlap of pk) 2,971,522 (when 2 tables have mutually exclusive pk / 2,971,522 = 586,648 + 2,384,874)

however, in actuality, above join yielding nbrcount = 24,898,361.

any pointers on why number of rows has blown up?

because pks have duplicate matches. easy enough find:

select pk, count(*) group pk having count(*) > 1;  select pk, count(*) b group pk having count(*) > 1; 

of course, can restrict conditions use filters.

and, perhaps want count(distinct coalesce(a.pk, b.pk)) in select clause. count number of rows being returned each table, no duplicates on rows.