CODEWITHSUNDEEP
sqloracle-database
Nvr
Nvr

Reputation: 171

How to rewrite query NOT IN with join condition

Below query having high cost, dur to NOT IN (sub-query)

select
    clm.column1,
    column2,
    ins.column3,
    dia.column4,
    clm.column5
From
    table1  clm 
    
    inner join table2 ins on clm.key = ins.key 
    
    left outer join table3 SFX ON
        clm.number = SFX.number
        and
        id in (
            select
                max(id)
            from
                table3
            group by number
        )
        AND
        clm.column1 NOT IN (
            sELECT
                column1
            FROM
                prod
        )
        and
        TO_CHAR( SFX.app_dt, 'YYYYMMDD' ) = to_char( '21-06-2020', 'YYYYMMDD' )
    
    left outer join (
        SELECT
            column1,
            RTRIM(
                XMLAGG( XMLELEMENT( E,MD.process, ',' ).EXTRACT( '//text()' ) ORDER BY column1 ).GetClobVal(), ','
            ) column4 
        FROM
            table4 D
            INNER JOIN table5 MD ON MD.key = D.id
        GROUP BY
            column1
    ) dia on clm.column1 = dia.column1 
where
    clm.column1 not in ( select column1 from prod );

I dont have idea how to rewrite the AND clm.column1 NOT IN(sELECT column1 FROM prod) with join condition.

If have any idea, please let me know.

Upvotes: 0

Views: 189

Answers (2)

Popeye
Popeye

Reputation: 35910

There are multiple issues in your query. I tried to resolve them as much as possible in following code:

select clm.column1, column2, ins.column3, dia.column4, clm.column5
From table1  clm 
     inner join table2 ins 
        on clm.key = ins.key 
     left outer join table3 SFX 
       ON clm.number = SFX.number
        -- DON'T USE SUB-QUERIES IN JOIN 
        -- ADDED IT IN THE WHERE CLAUSE
        --id in ( select max(id) from table3 group by number )
        -- WHY THIS SUBQUERY IS HERE. IT CAN BE IN LEFT JOIN OR WHERE 
        -- ADDED IN LEFT JOIN
        --AND clm.column1 NOT IN ( sELECT column1 FROM prod )
        --
        -- IF THERE IS INDEX ON SFX.app_dt THEN USE THE >= AND < AS FOLLOWS
        -- and TO_CHAR( SFX.app_dt, 'YYYYMMDD' ) = to_char( '21-06-2020', 'YYYYMMDD' )
        AND SFX.app_dt >= DATE '2020-06-21' AND SFX.app_dt < DATE '2020-06-22'
    left outer join (
        SELECT column1,
               RTRIM(
                XMLAGG( XMLELEMENT( E,MD.process, ',' ).EXTRACT( '//text()' ) 
                          ORDER BY column1 ).GetClobVal(), ','
            ) column4 
        FROM table4 D INNER JOIN table5 MD ON MD.key = D.id
        GROUP BY column1
    ) dia on clm.column1 = dia.column1 
    -- ADDED FOLLOWING LEFT JOIN
    LEFT JOIN PROD P ON P.COLUMN1 = CLM.COLUMN1
-- ADDED ENTIRE NEW WHERE CLAUSE
where P.COLUMN1 IS NULL
    AND (SFX.ID IS NULL OR SFX.id in ( select max(id) from table3 group by number) );

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175726

The pattern is:

SELECT DISTINCT ...
FROM table1  clm
LEFT JOIN prod
  ON clm.column1 = prod.column1
WHERE prod.column1 IS NULL

Upvotes: 0

Related Questions