Respuesta.

Historia del problema:

Los operadores EXISTS e IN se utilizan para filtrar registros basados en subconsultas. Desde el surgimiento de SQL, los desarrolladores se han enfrentado a la decisión entre ellos, tratando de entender qué método funciona más rápido y en qué casos es preferible su aplicación.

Problema:

La tarea principal es obtener solo aquellas filas que tienen una correspondencia en la tabla externa o interna, lo cual es siempre crítico para el rendimiento en conjuntos de datos grandes. La elección entre EXISTS e IN depende de la estructura de la subconsulta, la cantidad y unicidad de los valores devueltos, así como del SGBD utilizado.

Solución:

IN es generalmente más eficiente cuando la subconsulta devuelve un pequeño número de valores únicos.
EXISTS es preferible cuando solo importa el hecho de la existencia de filas correspondientes; es adecuado para subconsultas grandes que devuelven miles o millones de filas.
También hay que tener en cuenta el comportamiento específico con NULL y las diferencias en la optimización en diferentes sistemas de gestión de bases de datos.

Ejemplo de código:

-- Uso de IN
SELECT name FROM students WHERE id IN (SELECT student_id FROM enrollments WHERE course = 'SQL');

-- Uso de EXISTS
SELECT name FROM students WHERE EXISTS (SELECT 1 FROM enrollments WHERE enrollments.student_id = students.id AND enrollments.course = 'SQL');

Características clave:

EXISTS termina la búsqueda en la primera coincidencia (la subconsulta anidada puede detenerse).
IN es generalmente efectivo con listas cortas, pero puede ser lento con subconsultas largas y no únicas.
EXISTS funciona correctamente en presencia de NULL, IN puede comportarse de manera inesperada con NULL en la subconsulta.

Preguntas difíciles.

¿Qué pasará si la subconsulta IN encuentra NULL?

Muchos piensan que IN simplemente ignorará NULL, pero en presencia de NULL el resultado puede volverse impredecible. Por ejemplo, la consulta:

SELECT id FROM orders WHERE client_id IN (1, NULL, 2);

técnicamente no incluirá filas donde client_id no sea igual a 1 o 2, pero si en la lista de la subconsulta aparece solo NULL, el resultado será vacío.

¿EXISTS e IN son construcciones completamente intercambiables?

No. El uso de EXISTS suele ser más rápido, porque no es necesario analizar toda la subconsulta. Además, IN no funciona con subconsultas de múltiples columnas, mientras que EXISTS sí, ya que la comparación se hace por la condición en WHERE. Por ejemplo:

SELECT col1 FROM t1 WHERE (col1, col2) IN (SELECT col3, col4 FROM t2);

Esta variante a menudo no es compatible, mientras que EXISTS sí lo es.

¿Puede IN ser más rápido que EXISTS al trabajar con campos indexados?

Sí, si la subconsulta es pequeña y el campo comparado tiene un índice, IN puede resultar ser más rápido. Sin embargo, en conjuntos grandes o en ausencia de índice —a la inversa.

Errores típicos y anti-patrones

Usar IN con una subconsulta que devuelve una gran cantidad de filas.
Ignorar las consecuencias de la presencia de NULL en la subconsulta.
Sustituir EXISTS por IN como regla general sin analizar el plan de consulta.

Ejemplo de la vida real

Caso negativo

Un analista construyó un informe utilizando IN, sin tener en cuenta que la subconsulta devuelve cientos de miles de filas con NULL. El informe comenzó a tardar minutos, a veces perdiendo datos.

Pros:

La lógica es fácil de entender. Contras:
Pérdida en el rendimiento.
Riesgo de obtener datos incorrectos.

Caso positivo

La misma consulta se reescribió utilizando EXISTS con una condición adicional, se recalcularon los índices.