En teoría, EXISTS debe ser más rápido porque solo comprueba si existe una fila o no (Verdadero o Falso), por otro lado, IN debe recopilar el conjunto de resultados antes de probar la condición.
¿Es cierta la afirmación anterior?
Según mis pruebas de laboratorio, la respuesta es "Depende", básicamente el optimizador trata EXISTS e IN de la misma manera cuando es posible, pero depende del esquema de la tabla, datos y la forma en la que se escribió la consulta.
¿Cómo usarlo?
IN: (Subconsulta| Expresión[ ,...n ])
Expresión: puedes pasar una lista de literales IN (1,2,3, N) o un solo valor IN (1)
Subconsulta: es una subconsulta que tiene un conjunto de resultados de una columna. EN (SELECT A FROM B)
EXISTS: (Subconsulta)
Subconsulta: es una subconsulta para comparar la existencia de la fila (VERDADERO o FALSO), el conjunto de resultados no importa.
El Lab (IN vs EXISTS)
Preparemos el entorno, para ello ejecute el siguiente script para crear dos tablas:
Tenemos dos tablas, "MyMainTable" (300K filas) es nuestra tabla principal y "MySubTable" (25% de datos aleatorios de "MyMainTable") esta es la tabla que vamos a utilizar para filtrar los datos.
Veamos cómo EXISTS y IN se comportan.
Note que he agregado una división entre cero en la declaración EXISTS, es probable que estés esperando un error, pero lo interesante es que la lista de campos en una declaración EXISTS no se ejecutará y la consulta se ejecutará.
He forzado esas consultas para que no se ejecuten en paralelo, esto es para obtener un plan de ejecución muy simple que podamos comparar fácilmente.
Aquí están los planes de ejecución y son idénticos , dos clustered index scan y un hash match.
Los IOs también son exactamente iguales.
Así que el optimizador de consultas trató ambas consultas de la misma manera. No hay diferencias de rendimiento aquí.
Agreguemos algunos índices y veamos cómo se comportan.
Los planes de ejecución son idénticos, aunque ahora tenemos un index scan, un stream aggregate y un merge join debido a los nuevos índices.
Los IOs también son exactamente iguales.
Hasta ahora, el IN y el EXISTS parecen funcionar de manera idéntica, sin importar si hay un índice o no, incluso cuando hay valores nulos en la subconsulta o en la tabla externa.
¿Qué hay de NOT IN vs NOT EXISTS?
Veamos cómo NO EXISTS y NOT IN se comportan. Tenga en cuenta que ya he creado algunos índices.
Una vez más, los IOs y los planes de ejecución son exactamente iguales.
Hasta ahora parecen actuar de forma idéntica.
Espera ... El problema potencial con valores NULL.
Si tiene un valor NULL en el resultado de la subconsulta o en la expresión, IN y EXISTS darán el mismo conjunto de resultados, pero NOT IN se comporta de manera diferente.
Vamos a alterar las columnas en nuestras tablas.
Veamos cómo NOT EXISTS y NOT IN se comportan una vez más.
Los planes de ejecución son muy diferentes, se puede ver que el NOT IN tiene mal desempeño.
Los IOs muestran un rendimiento muy malo con el NOT IN. Tenga en cuenta que incluso con este mal rendimiento seguimos recuperando el mismo número de filas.
¿Qué pasa si insertamos un valor NULL?
Insertemos un valor NULL.
El plan no cambió, aun podemos ver el mismo mal desempeño para el NOT IN.
Los IOs también son exactamente iguales... Espera, ¿qué pasó aquí?
El NOT IN no devolvió filas !!! ¿Cómo es esto posible? La respuesta es muy simple, por favor ver más abajo.
IN se convierte en OR (=).
NOT IN se convierte AND (<>).
Por otro lado, NOT EXISTS funciona bien ya que solo verifica si existe la fila o no (Verdadero o Falso)
Regla de oro.
Si va a utilizar una subconsulta, debe utilizar EXISTS / NOT EXISTS.
Si va a utilizar una lista de valores literales, debe utilizar IN / NOT IN.
¡Diviértanse aprendiendo!
Commentaires