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.