Duda clásica que sigue generando debate: en una subconsulta de Oracle, ¿EXISTS o IN? Van las diferencias reales, no solo la teoría.

Con IN, Oracle típicamente evalúa la subconsulta completa y compara el valor contra toda la lista de resultados:

SELECT nombre
FROM clientes c
WHERE c.id_cliente IN (SELECT id_cliente FROM pedidos WHERE total > 1000);

Con EXISTS, Oracle puede parar en cuanto encuentra la primera fila que cumple la condición, sin necesidad de traer el conjunto completo:

SELECT nombre
FROM clientes c
WHERE EXISTS (SELECT 1 FROM pedidos p WHERE p.id_cliente = c.id_cliente AND p.total > 1000);

Cuándo usar cada uno, en la práctica:

EXISTS suele ganar cuando la subconsulta puede devolver muchas filas, porque no hace falta materializarlas todas, solo confirmar que existe al menos una. También es la opción más segura cuando la subconsulta puede devolver NULL: NOT IN con una subconsulta que devuelve algún NULL puede dar resultados inesperados (la comparación con NULL nunca es ni verdadera ni falsa, es desconocida, y eso puede hacer que NOT IN no devuelva ninguna fila cuando tú esperabas que devolviera varias). NOT EXISTS no tiene ese problema.

IN puede salir mejor cuando la lista de la subconsulta es pequeña y fija, o cuando la tabla externa (la que filtras) es mucho más pequeña que la subconsulta, porque en ese caso da igual cuántas filas devuelva la subconsulta.

En la práctica, hoy en día el optimizador de Oracle suele reescribir internamente IN y EXISTS de forma parecida cuando puede (transformación de subconsultas), así que la diferencia de rendimiento real muchas veces es menor de lo que la teoría clásica sugiere. Mi consejo práctico: usa EXISTS/NOT EXISTS por defecto (es más seguro con NULLs y suele leerse mejor cuando la condición depende de la fila externa), y mira el EXPLAIN PLAN real de tu caso concreto si el rendimiento te preocupa, en vez de fiarte de la regla general.

¿Alguna experiencia real donde uno os sorprendiera frente al otro?

Angel Carrero

Sobre el aviso de NOT IN con NULL que se menciona: merece la pena ver un ejemplo concreto, porque sorprende la primera vez que te pasa. Imagina que quieres los clientes que no tienen ninguna factura pendiente:

SELECT nombre FROM clientes
WHERE id_cliente NOT IN (SELECT id_cliente FROM facturas_pendientes);

Si facturas_pendientes.id_cliente tiene aunque sea una sola fila con NULL (por ejemplo, una factura huérfana sin cliente asignado), esta consulta no devuelve absolutamente ninguna fila, aunque tengas cientos de clientes sin ninguna factura pendiente. La razón: comparar cualquier valor contra NULL con = o <> no da ni verdadero ni falso, da «desconocido», y eso contamina toda la evaluación de NOT IN.

Con NOT EXISTS este problema no existe, porque la comparación va fila a fila de forma correlacionada, no contra una lista completa:

SELECT nombre FROM clientes c
WHERE NOT EXISTS (
    SELECT 1 FROM facturas_pendientes f WHERE f.id_cliente = c.id_cliente
);

Este es, probablemente, el motivo más convincente para preferir EXISTS/NOT EXISTS por defecto: no es solo una cuestión de rendimiento, es que NOT IN con datos que puedan tener NULL es directamente una fuente de bugs silenciosos.

David Carrero