EXISTS and join

From: Carlos Alberto <calberto2312_at_hotmail.com>
Date: 29 Jul 2002 11:22:21 -0700
Message-ID: <72954535.0207291022.340a2f42@posting.google.com>

Hi all,  

   I have a very bad performance query using EXISTS, and I was wondering to change it to a join. Itīs like this :

select clie.unite_commercialisation,

       clie.type_client, clie.code_devise from pi_client clie,

     pi_contrat_v_3 cont
where nvl(cont.reference_client_benef,' ') = clie.reference_client   and cont.etat_contrat = 'FIN'
  and exists

     (select null
      from pi_facture fact
      where fact.numero_contrat like
substr(cont.numero_contrat,1,9)||'%'
        and nvl(fact.date_annulation,0) = 0
        and nvl(fact.date_reglement,0) = 0)

and it would be like this :

select clie.unite_commercialisation,

       clie.type_client, clie.code_devise

from pi_client clie,
     pi_contrat_v_3 cont,
     pi_facture fact

where nvl(cont.reference_client_benef,' ') = clie.reference_client   and cont.etat_contrat || '' = 'FIN'
  and fact.numero_contrat like substr(cont.numero_contrat,1,9)||'%'   and nvl(fact.date_annulation,0) = 0
  and nvl(fact.date_reglement,0) = 0

     The performance of the join query was better than with EXISTS clause. But I read in some books that sometimes I cannot make this change because the result set would be different. Itīs true or not? Must I compare both result sets?

Thanks in advanced,
Carlos Received on Mon Jul 29 2002 - 13:22:21 CDT