NOT IN y NOT EXISTS no son lo mismo.

Aunque no lo parezca, NOT IN y NOT EXISTS no son cláusulas equivalentes. IN y EXISTS devuelven el mismo conjunto de resultados, pero su semántica es completamente distinta. Aun así, los programadores la utilizan indistintamente ya que se han acostumbrado a trabajar con una de las dos y realizan de igual modo la negación.

Por un lado, IN solicita aquellos registros cuyo valor exista dentro de un conjunto de valores. Por otro lado, la cláusula EXISTS incluye el registro si la subconsulta siguente devuelve un valor, sea cual sea, incluído NULL.

De este modo, la sintaxis de IN y EXISTS podrían aprobar los mismos registros si la consulta está bien construída, aunque los planes de ejecución sean completamente distintos.

NOT IN y NOT EXISTS no son sinónimos. El valor NULL determina la diferencia.

Dada la siguiente consulta con NOT IN:


SQL> select count(*) from clientes
  2  where ciu_id_ciudad not in (select ciu_id_ciudad from agencias);

  COUNT(*)
———-
         0

Y su equivalente con NOT EXISTS

SQL> select count(*) from clientes c
  2  where not exists (select a.ciu_id_ciudad from agencias a where c.ciu_id_ciudad=a.ciu_id_ciudad);

  COUNT(*)
———-
         0

Los resultados son idénticos: Efectivamente, no existe ningún cliente que viva en una ciudad donde no haya una agencia. En este caso, hay clientes y agencias en todas las ciudades de la base de datos.

No obstante, si las tablas contienen elementos nulos, las dos operaciones devolverán conjuntos de resultados distintos.

NOT IN y NOT EXISTS no son la misma cláusula con sintaxis distinta.

SQL> update clientes
  2  set ciu_id_ciudad=null
  3  where round(rownum/1000)=0;

499 filas actualizadas.

SQL> select count(*) from clientes
  2  where ciu_id_ciudad not in (select ciu_id_ciudad
  3                              from agencias);

  COUNT(*)
———-
         0

SQL> select count(*) from clientes c
  2  where not exists (select a.ciu_id_ciudad
  3   from agencias a where c.ciu_id_ciudad=a.ciu_id_ciudad);

  COUNT(*)
———-
       499

Así, NOT IN se basa en la consulta de todos los valores respecto un valor concreto para determinar que no está incluido en la lista. Los valores nulos se computan como FALSO y no incluyen el elemento en la selección.

NOT EXISTS se basa en la consulta de un valor y su no existencia en un subconjunto de registros. Los valores nulos se computan como VERDADERO, e incluyen el elemento en la selección.

standardjulio 24adminAdministración16 Comment

Comments

  1. Luis.-
    • 21 diciembre, 2006
    • 2:21 pm
    • Reply

    Claro y conciso. Gracias.

  2. Javier Morales
    • 21 diciembre, 2006
    • 4:28 pm
    • Reply

    Gracias a ti, Luis!
    Por leerme y por tu comentario! 🙂

    Pasa unas felices fiestas.

  3. Antonio F.
    • 11 enero, 2007
    • 12:15 pm
    • Reply

    Quizás añadir que «Not Exists» tiene un mejor rendimiento que el «NOT IN» al no necesitar materializar el resultado.

    Antonio

  4. Anónimo
    • 9 octubre, 2009
    • 3:55 pm
    • Reply

    Javie, muy claro en tus conceptos. Me han sido de mucha utilidad.
    Muchas Gracias.

  5. aaaaa
    • 1 diciembre, 2009
    • 2:34 pm
    • Reply

    Buenos Dias Javier,

    Encuentro muy interesante sus explicaciones en su blog.

    Tengo una secuencia sql que quiero optimizar, pero no encuentro que mas hacerle. Agradeceria sus recomendaciones.

    Gracias

    claudia

    —
    SELECT
    '(' || X.CODE_DA || ')' CODE_DA,
    to_char(X.NO_CLIEN,'fm00000009') no_clien,
    RTRIM(C.NOM)||' '||RTRIM( C.NOM_2)NOM,
    CC.NO_UAR,
    E.DESCR_ABREG,
    X.DATE_SAISI DATE_SAISIE,
    X.DATE_COMPT DATE_COMPTABLE,
    X.CODE_PERIO,
    X.CODE_TYPE_TRX,
    X.NO_NOTE_DT_CT_ENCAI,
    DECODE(SUBSTR(X.CODE_TYPE_TRX,4,1), 'C',NVL(DECODE(T.CODE_NATUR_CPTE_CLIEN,'D',X.MNT_TOT,'C',X.MNT_TOT * -1),0),0) MNT_NOTE,
    DECODE(SUBSTR(X.CODE_TYPE_TRX,4,1), 'A',NVL(DECODE(T.CODE_NATUR_CPTE_CLIEN,'D',X.MNT_TOT,'C',X.MNT_TOT * -1),0),0) MNT_NOTE_ANN,
    N.CODE_CLASS_CPTE_CLIEN,
    DECODE (N.TYPE_PAIEM, 'S', 'Paiement en ligne', 'V', 'Virement', 'T', 'TPV', 'C', 'Compte', N.TYPE_PAIEM) TYPE_PAIEM, /* MODIF Jason Wong 01/10/2003 */
    DECODE (N.TYPE_CARTE, 'V', 'VISA', 'M', 'Mastercard', 'A', 'American Express', 'D', 'Débit', 'C', 'Compte bancaire', N.TYPE_CARTE) TYPE_CARTE, /* MODIF Jason Wong 01/10/2003 */
    N.NO_COMMA_COE,
    T.DESCR_ABREG,
    N.CODE_NATUR CODE_NATURE,
    A.DESCR||' ('||A.CODE_DA||')' Domaine,
    N.NO_REFER NNOREFER,
    N.REF NREF,
    ltrim((N.DATE_TRX||' '||N.CODE_TRX||' '||N.CODE_MINIS||' '||N.REF||' '||N.COM), ' ') NCOM
    FROM REV_DA A,
    REV_TYPE_EVENE E,
    REV_TYPE_TRX T,
    REV_NOTE_DT_CT_ENCAI N,
    REV_TRX X,
    REV_CPTE_CLIEN CC,
    REV_CLIEN_TAB C

    WHERE C.NO_CLIEN = X.NO_CLIEN
    and x.no_clien = n.no_clien
    AND X.NO_CLIEN = CC.NO_CLIEN

    AND N.CODE_DA = C.CODE_DA
    AND X.CODE_DA = C.CODE_DA
    AND X.CODE_DA = CC.CODE_DA
    AND X.CODE_DA = N.CODE_DA
    AND A.CODE_DA = X.CODE_DA

    AND X.CODE_CLASS_CPTE_CLIEN = CC.CODE_CLASS_CPTE_CLIEN
    AND N.CODE_NATUR = DECODE(:P_CODE_DT_CT,'DÉBIT', 'D','CRÉDIT','C', N.CODE_NATUR) /* MODIF 09/07/03 */
    AND N.STATU = DECODE(:P_STATUT, '%', N.STATU, :P_STATUT)
    AND N.CODE_TYPE_EVENE = E.CODE_TYPE_EVENE

    AND X.NO_NOTE_DT_CT_ENCAI = N.NO_NOTE_DT_CT_ENCAI
    AND X.AN_EXERC = :AN_EXERC
    AND X.CODE_PERIO >= :PER_1
    AND X.CODE_PERIO <= :PER_2
    AND X.CODE_TYPE_TRX = T.CODE_TYPE_TRX
    AND ((:P_COE = 'CE' AND N.NO_COMMA_COE is not null) OR (:P_COE = '%')) /*Modif 08/07/03) */

    and x.code_class_cpte_clien = n.code_class_cpte_clien

  6. Javier Morales
    • 3 diciembre, 2009
    • 7:56 am
    • Reply

    Hola Claudia,

    Tiene pinta de ser una consulta en estrella… ¿puedes mandarme el plan de ejecución por mail? ¿puedes ver si Oracle está ejecutando esa consulta como estrella? ¿tienes el parámetro star_transformation_enable a TRUE?

    hazle una visita a mi otro blog http://optimizacionsql.blogspot.com para que veas a qué me refiero con el plan de ejecución
    (con la query sola, no podemos hacer nada…)

    Un saludo

  7. eustaki0
    • 20 octubre, 2010
    • 2:42 pm
    • Reply

    Muchas Gracias, Javier.
    Muy útil la info. Ahora a esperar a que termine la ejecución.
    Salut!

  8. Anónimo
    • 14 noviembre, 2010
    • 3:37 pm
    • Reply

    javier quiero hacerte una pregunta sobre programacion en access. resulta que yo tengo tablas creadas en fox, y debo importar esas tablas a acces para trabajarlas, me dijeron q hay dos formas, una es cambiar el tipo dela tabla desde fox poniendole tipo fox2x y la otra es la que estoy interesada en saber que me dijeron que se realiza desde access. gracias!!!!
    Luciana

  9. Javier Morales
    • 15 noviembre, 2010
    • 10:24 am
    • Reply

    Luciana: Lo siento, no tengo idea de Access. Como puedes ver, el blog está orientado principalmente a Oracle, que es mi área de conocimiento.

  10. Xeox
    • 2 enero, 2012
    • 7:07 pm
    • Reply

    Muy interesante tu comentario.

    Saludos desde Chile.

  11. DARIO QUEMERO
    • 5 julio, 2012
    • 3:37 pm
    • Reply

    si existe un cliente que no se le cargo la ciudad (en ese campo tiene NULL) si es equivalente.
    Nunca un campo ID debe quedar con valor NULL

  12. Javier
    • 6 julio, 2012
    • 7:23 am
    • Reply

    Hola Darío,

    Es un modelo de ejemplo, en que el dato de la ciudad de un cliente puede no estar informado (no es el ID de clave primaria, sino la ciudad a la que pertenece, que puede ser desconocida).

    El ejemplo es sólo para ilustrar que NOT IN y NOT EXISTS pueden devolver resultados distintos por cómo evalúan el valor NULL.

  13. Javier
    • 6 julio, 2012
    • 7:23 am
    • Reply

    Este comentario ha sido eliminado por el autor.

  14. Mootzo García
    • 29 julio, 2012
    • 2:12 am
    • Reply

    Gracias me sirvio muchisimo para una consulta 🙂 bueno, sub consulta 😀

  15. Roy
    • 6 mayo, 2014
    • 7:48 pm
    • Reply

    en el Reporting Services Tengo un parámetro que llena una colección de Nros., y cuando envío los valores a ese parámetro por medio de una variable de VB, no realiza el filtro del Reporte, excepto si la variable envía un valor. WHERE Cliente.Id IN (@ParametroCliente)

    Ayuda Por Favor. Gracias

  16. oscar
    • 7 mayo, 2016
    • 8:50 pm
    • Reply

    Buenas tardes, espero me puedan ayudar, resulta que tengo una consulta con NOT EXITS y al principio me funcionaba pero ahora ya no, funciona igual que EXITS, no le he movido nada a mi consulta, no se que pueda ser, espero me puedan ayudar

Deja una respuesta

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.