lunes, julio 25, 2005

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.

16 comentarios:

Luis.- dijo...

Claro y conciso. Gracias.

Javier Morales dijo...

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

Pasa unas felices fiestas.

Antonio F. dijo...

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

Antonio

Anónimo dijo...

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

aaaaa dijo...

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

Javier Morales dijo...

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

eustaki0 dijo...

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

Anónimo dijo...

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

Javier Morales dijo...

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

Xeox dijo...

Muy interesante tu comentario.

Saludos desde Chile.

DARIO QUEMERO dijo...

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

Javier dijo...
Este comentario ha sido eliminado por el autor.
Javier dijo...

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.

Mootzo García dijo...

Gracias me sirvio muchisimo para una consulta :) bueno, sub consulta :D

Roy dijo...

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

oscar dijo...

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

Amazon