Search code examples
oracle-databaseleft-join

Oracle comparing data between 2 tables

I am having 2 tables like below:

Table1:

PP CP LINNUM
4610-A4N3C-K0235 18961-060 900000021
4610-A4N3C-K0235 22873 900000028
4610-A4N3C-K0235 23282-10 900000038
4610-A4N3C-K0235 23282-10 900000044

Table2:

PP CP LINNUM
4610-A4N3C-K0235 18961-060 900000021
4610-A4N3C-K0235 22873 900000028
4610-A4N3C-K0235 23282-10 900000038
4610-A4N3C-K0235 23282-10 900000044

I am using following query to compare PP,CP columns from both tables and check if linnum matches or not. I have total of 126 entries in both tables and expecting output with 126 entries.

select a.pp,a.cp,a.linnum,b.pp,b.pp,b.linnum,
case when a.pp=b.pp and a.cp=b.cp and a.linnum=b.linnum then 'Y' Else 'N' end matchnomatch
from tt1 a inner join tt2 b
on (a.pp=b.pp and a.cp=b.cp);

OUTPUT:

A.PP A.CP A.LINNUM B.PP B.CP B.LINNUM MATCHNOMATCH
4610-A4N3C-K0235 18961-060 900000021 4610-A4N3C-K0235 18961-060 900000021 Y
4610-A4N3C-K0235 22873 900000028 4610-A4N3C-K0235 22873 900000028 Y
4610-A4N3C-K0235 23282-10 900000038 4610-A4N3C-K0235 23282-10 900000038 Y
4610-A4N3C-K0235 23282-10 900000044 4610-A4N3C-K0235 23282-10 900000038 N
4610-A4N3C-K0235 23282-10 900000038 4610-A4N3C-K0235 23282-10 900000044 N
4610-A4N3C-K0235 23282-10 900000044 4610-A4N3C-K0235 23282-10 900000044 Y

though everything in both the tables match, I am getting 292 entries as output with even N as output in matchnomatch column. What is the best way to do this?

I am expecting following output alone ExpectedOutput:

A.PP A.CP A.LINNUM B.PP B.CP B.LINNUM MATCHNOMATCH
4610-A4N3C-K0235 18961-060 900000021 4610-A4N3C-K0235 18961-060 900000021 Y
4610-A4N3C-K0235 22873 900000028 4610-A4N3C-K0235 22873 900000028 Y
4610-A4N3C-K0235 23282-10 900000038 4610-A4N3C-K0235 23282-10 900000038 Y
4610-A4N3C-K0235 23282-10 900000044 4610-A4N3C-K0235 23282-10 900000044 Y

If the linnum does not really match thought pp and cp match, it should give N. How can this be done in oracle sql.

Solution

  • LEFT JOIN on all 3 columns and then check if a match was found in the CASE expression based on the existence of a joined row:

    SELECT t1.*,
       CASE WHEN t2.pp IS NOT NULL THEN 'Y' ELSE 'N' END AS matchnomatch
FROM   table1 t1
       LEFT OUTER JOIN table2 t2
       ON     t1.pp = t2.pp
          AND t1.cp = t2.cp
          AND t1.linnum = t2.linnum;

    Which outputs:

    PP CP LINNUM MATCHNOMATCH
    4610-A4N3C-K0235 18961-060 900000021 Y
    4610-A4N3C-K0235 22873 900000028 Y
    4610-A4N3C-K0235 23282-10 900000038 Y
    4610-A4N3C-K0235 23282-10 900000044 Y

    Or, if you only want the rows from table1 and no duplicates if there are multiple matches between table1 and table2 then select only from table1 and use EXISTS to check for matches in table2:

    SELECT t1.*,
       CASE WHEN EXISTS (SELECT 1
                         FROM   table2 t2
                         WHERE  t1.pp = t2.pp
                         AND    t1.cp = t2.cp
                         AND    t1.linnum = t2.linnum)
       THEN 'Y'
       ELSE 'N'
       END AS matchnomatch
FROM   table1 t1;

    Which, for the sample data, outputs the same as above.

    fiddle