Re: Need assistance in converting subqueries to joins

[Adrian Klaver <adrian.klaver@xxxxxxxxxxx>]

On 9/19/24 21:07, Siraj G wrote:
> Hello Tech gents!
>
> I am sorry if I am asking the wrong question to this group, but wanted 
> assistance in converting a query replacing subqueries with joins.
>
> Please find the query below (whose cost is very high):

Add the output of the EXPLAIN ANALYZE for the query.

> select em_exists_id from IS_SEC_FILT WHERE (IS_SEC_FILT_GUID) NOT IN 
> (SELECT IS_OBJ_GUID FROM TMP_IS_SEC_FILT T0, IS_PROJ P0 WHERE 
> T0.IS_PROJ_GUID = P0.IS_PROJ_GUID AND P0.IS_PROJ_ID = 
> IS_SEC_FILT.IS_PROJ_ID) AND (IS_PROJ_ID) IN (SELECT IS_PROJ_ID FROM 
> IS_PROJ P0, TMP_IS_SEC_FILT T0, EM_MD R0 WHERE T0.IS_REPOSITORY_GUID = 
> R0.REP_GUID AND T0.IS_PROJ_GUID = P0.IS_PROJ_GUID AND 
> P0.IS_REPOSITORY_ID = R0.REP_ID);

For future reference formatting the query here:

https://sqlformat.darold.net/

helps get it into a form that is easier to follow:

SELECT
    em_exists_id
FROM
    IS_SEC_FILT
WHERE (IS_SEC_FILT_GUID)
NOT IN (
    SELECT
        IS_OBJ_GUID
    FROM
        TMP_IS_SEC_FILT T0,
        IS_PROJ P0
    WHERE
        T0.IS_PROJ_GUID = P0.IS_PROJ_GUID
        AND P0.IS_PROJ_ID = IS_SEC_FILT.IS_PROJ_ID)
AND (IS_PROJ_ID) IN (
    SELECT
        IS_PROJ_ID
    FROM
        IS_PROJ P0,
        TMP_IS_SEC_FILT T0,
        EM_MD R0
    WHERE
        T0.IS_REPOSITORY_GUID = R0.REP_GUID
        AND T0.IS_PROJ_GUID = P0.IS_PROJ_GUID
        AND P0.IS_REPOSITORY_ID = R0.REP_ID);


> Regards
> Siraj

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx