CODEWITHSUNDEEP
oracle-databaseplsql
Wahid Masud
Wahid Masud

Reputation: 1093

picking 1 column from 2 tables and comparing them

In my Oracle database there are two tables which are TEMP_HR and PAY_SLIP_APR_16. Both of them have a common column named EMP_ID. TEMP_HR has over 10,000 records and PAY_SLIP_APR_16 has around 6,000 records. I want to know how many EMP_ID of PAY_SLIP_APR_16 is matched with TEMP_HR. If any ID doesn't match then print it. And here is my simple approach but I think its a very bad approach. So any faster method? 

DECLARE
    INPUT_EMP_NO VARCHAR2(13 BYTE);
    INPUT_EMP_ID VARCHAR2(13 BYTE);
    ROW_COUNT_1 NUMBER(6,0);
    ROW_COUNT_2 NUMBER(6,0);
    MATCHED_ID NUMBER;
    UNMATCHED_ID NUMBER;

BEGIN

    ROW_COUNT_1:=0;
    ROW_COUNT_2:=0;
    MATCHED_ID:=0;
    UNMATCHED_ID:=0;
    SELECT COUNT(*) INTO ROW_COUNT_1 FROM PAY_SLIP_APR_16;
    SELECT COUNT(*) INTO ROW_COUNT_2 FROM TEMP_HR;
    FOR A IN 1..ROW_COUNT_1 LOOP
    BEGIN
        SELECT EMP_ID INTO INPUT_EMP_ID FROM (SELECT EMP_ID, ROWNUM AS RN FROM PAY_SLIP_APR_16) WHERE RN=A;
        FOR B IN 1..ROW_COUNT_2 LOOP
            SELECT EMP_NO INTO INPUT_EMP_NO FROM (SELECT EMP_NO, ROWNUM AS RON FROM TEMP_HR) WHERE RON=B;
            IF(INPUT_EMP_ID=INPUT_EMP_NO)THEN
                MATCHED_ID:=MATCHED_ID+1;
                EXIT;
            ELSE
                CONTINUE;
            END IF;
        END LOOP;
        UNMATCHED_ID:=UNMATCHED_ID+1;
        DBMS_OUTPUT.PUT_LINE(INPUT_EMP_ID);
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(INPUT_EMP_ID||' -> '||SQLERRM);
    END;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('MATCHED -> '||MATCHED_ID);
    DBMS_OUTPUT.PUT_LINE('UNMATCHED -> '||UNMATCHED_ID);
END;

Upvotes: 1

Views: 37

Answers (2)

Bohemian
Bohemian

Reputation: 425003

Use an outer join filtering for missed joins:

select p.*
from PAY_SLIP_APR_16 p
left join TEMP_HR t on t.EMP_ID = p.EMP_ID
where t.EMP_ID is null

An index on TEMP_HR(EMP_ID) will make this query fly.

Upvotes: 2

J. Chomel
J. Chomel

Reputation: 8395

You should use SQL sets!

To check which EMP_ID isn't in TEMP_HR you may try this:

select EMP_ID FROM PAY_SLIP_APR_16 
 where EMP_ID not in (select EMP_NO from TEMP_HR);

Then the other way around:

select EMP_NO FROM TEMP_HR 
 where EMP_NO not in (select EMP_ID from PAY_SLIP_APR_16);

Upvotes: 0

Related Questions