Avatar of CRISTIANO_CORRADI
CRISTIANO_CORRADI asked on

Conversion from "NOT IN", "IN" ....... to "NOT EXISTS", "EXISTS".

In order to optimize the query, I tried to rewrite the following query:

   UPDATE STRUCTURE x
            SET (x.ui_res_calcolate, x.ui_bus_calcolate,
                 x.ui_pesate_calcolate, x.coppie_attestate,
                 x.coppie_impegnate, x.res, x.bus, x.td, x.cdn,
                 x.linea_pronta, x.linea_aggiuntiva, x.altro, x.desc_via,
                 x.nome_ra, x.accessibility) =
                   (SELECT a.nui_res, a.nui_bus, ROUND (a.nui_pesate),
                           a.coppie_attestate, a.coppie_impegnate, a.res,
                           a.bus, a.td, a.cdn, a.linea_pronta,
                           a.linea_aggiuntiva, a.altro,
                              TRIM (a.particella_distributore)
                           || ' '
                           || TRIM (a.via_distributore)
                           || ', '
                           || TRIM (a.civico_distributore),
                           b.codice_distributore, a.accessibilita
                      FROM planning_distributori_ext a, planning_sas_centrali b
                     WHERE UPPER (a.adc) IS NOT NULL
                       AND UPPER (a.adc) NOT IN
                                         ('DISTRIBUTORE', 'AREA DI CENTRALE')
                       AND b.codice_distributore =
                                                a.adc || '_' || a.distributore
                       AND b.objectid_ne = x.objectid)
          WHERE x.objectid IN (
                   SELECT b.objectid_ne
                     FROM planning_distributori_ext a, planning_sas_centrali b
                    WHERE UPPER (a.adc) IS NOT NULL
                      AND UPPER (a.adc) NOT IN
                                         ('DISTRIBUTORE', 'AREA DI CENTRALE')
                      AND b.codice_distributore =
                                                a.adc || '_' || a.distributore);

.... in this way:

UPDATE STRUCTURE x
   SET (x.ui_res_calcolate, x.ui_bus_calcolate, x.ui_pesate_calcolate,
        x.coppie_attestate, x.coppie_impegnate, x.res, x.bus, x.td, x.cdn,
        x.linea_pronta, x.linea_aggiuntiva, x.altro, x.desc_via, x.nome_ra,
        x.accessibility) =
          (SELECT a.nui_res, a.nui_bus, ROUND (a.nui_pesate),
                  a.coppie_attestate, a.coppie_impegnate, a.res, a.bus, a.td,
                  a.cdn, a.linea_pronta, a.linea_aggiuntiva, a.altro,
                     TRIM (a.particella_distributore)
                  || ' '
                  || TRIM (a.via_distributore)
                  || ', '
                  || TRIM (a.civico_distributore),
                  b.codice_distributore, a.accessibilita
             FROM planning_distributori_ext a, planning_sas_centrali b
            WHERE a.adc IS NOT NULL
              AND UPPER (a.adc) != 'AREA DI CENTRALE'
              AND UPPER (a.adc) != 'DISTRIBUTORE'
              AND b.codice_distributore = a.adc || '_' || a.distributore
              AND b.objectid_ne = x.objectid)
 WHERE EXISTS (
          SELECT 'X'
            FROM planning_distributori_ext alpha, planning_sas_centrali beta
           WHERE alpha.adc IS NOT NULL
             AND UPPER (alpha.adc) != 'AREA DI CENTRALE'
             AND UPPER (alpha.adc) != 'DISTRIBUTORE'
             AND beta.codice_distributore =
                                        alpha.adc || '_' || alpha.distributore
             AND beta.objectid_ne = x.objectid);

Could you help me to understand if these two queries have the same result set?
Oracle DatabaseSQL

Avatar of undefined
Last Comment
CRISTIANO_CORRADI

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

yes, the 2 updates will result in the same rows to be updated.
the difference will eventually be a better performance for the EXISTS() version.
ASKER
CRISTIANO_CORRADI

What reasoning have you done to state that the two queries are semantically equivalent?
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
CRISTIANO_CORRADI

Okay!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
CRISTIANO_CORRADI

10KU ;)