IN vs EXISTS - Which one is faster?

Theoretically EXISTS must be faster because it just checks for whether a row exists or not (True or False), on the other hand, IN has to collect the result set before testing the condition.

Is the above statement true?

Based on my labs, the answer would be “It depends”, basically the optimizer treats EXISTS and IN the same way when possible, but it depends of your table schema, data and the way you wrote the query.

How to use it?

IN: (subquery | expression [ ,...n ])

• Expression : you can pass a list of literal values IN (1,2,3,N) or a single value IN (1)

• Subquery: Is a subquery that has a result set of one column. IN (SELECT A FROM B)

EXISTS: (Subquery)

• Subquery: Is a subquery to compare the existence of the row (TRUE or FALSE), the result set does not matter.

The Lab (IN vs EXISTS)

Let’s prepare the environment, to do so run the following script to create two tables:

We have two tables, the “MyMainTable” (300K rows) is our main table and “MySubTable” (25% random data from “MyMainTable”) this the table that we are going to use to filter the data.

Let’s see how EXISTS and IN compare.

Please noted the division by zero in the EXISTS statement, at this point you are probably expecting an error, but the cool thing is that the field list in an EXISTS statement is not going to be executed and query will be executed.

I have forced those queries to do not run in parallel, this is to get a very simple execution plan that we can compare easily.

Here are the execution plans, noted they are identical, two clustered index scan and a hash match.

The IOs are also exactly the same.

So the query optimizer treated both queries the same way. No performance differences here.

Let’s add some indexes and see how it goes.

The execution plans are identical, though now we have an index scan, a stream aggregate and a merge join due to the new indexes.

The IOs are also exactly the same.

So far the IN and EXISTS appear to perform identically, not matter if there is an index or not, even when there are nulls in either the subquery or in the outer table.

What about NOT IN vs NOT EXISTS?

Let’s see how NOT EXISTS and NOT IN compare. Please noted that I have created some indexes already.

One more time, IOs and execution plans are also exactly the same.

So far they appear to perform identically.

Hang on… The potential issue with NULL values.

If you have a NULL value in the result of the subquery or expression, IN and EXISTS will give the same result set, but NOT IN behaves differently.

Let’s alter the columns on our tables.

Let’s see how NOT EXISTS and NOT IN compare one more time.

Very different execution plans, you can see the NOT IN has bad performance.

The IOs shows exactly the same, very bad performance using the NOT IN, please noted that even with the bad performance we are still retrieving the same number of rows.

What if we insert a null value?

Let’s insert a NULL value.

The plan did not change, we still have a bad plan for the NOT IN.

The IOs are the same… Hang on, what happened here?

The NOT INT did not return rows!!! How is this possible? The answer is very simple, please see below.

IN Resolves to OR (=).

NOT IN Resolved to AND (<>).

On the other hand, NOT EXISTS work just fine since it just checks for whether a row exists or not (True or False).

Rule of thumb.

• If you are going to use a subquery, then you must use EXISTS/NOT EXISTS.

• If you are going to use a list of literal values, then you must use IN/NOT IN.

Enjoy!!

IN vs EXISTS - ¿Cuál es más rápido?

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!

Fases lógicas para el procesamiento de consultas.

Este es uno de los conceptos más desafiantes que tuve que aprender cuando comencé a trabajar con SQL.

No me avergüenza confesar que me tomó algo de tiempo asimilar la forma en que SQL Server procesa su código y esto se debe a que SQL Server no procesa el código de arriba para abajo (como la mayoría de los lenguajes de programación).

Soy un DBA por Accidente, por lo que esperaba que SQL Server procesara el código como lo hacen otros lenguajes de programación.

El “keyed-in order"

Este es el orden en el que se supone que debes escribir las cláusulas de consulta. Por favor ver más abajo.

Fases lógicas para El procesamiento de consultas

Aquí es donde comienza la diversión y esto es porque el orden de procesamiento de la consulta es diferente. Comienza con la cláusula FROM.

Aquí está la misma consulta basada en el "orden lógico":

1. Cada fase opera en una o más tablas como entradas y devuelve una tabla virtual como salida.

2. La tabla de salida de una fase es la entrada a la siguiente fase.

3. El último paso devuelve el resultado final de la consulta.

Si aprendez de forma visual como yo, dale una mirada a el diagrama de flujo de Itzik Ben-Gan para "Logical Query Processing".

Ejemplos:

Un error común es tratar de usar un ALIAS en la cláusula WHERE, esto generará un error porque la cláusula SELECT se evalúa después de una cláusula WHERE, por lo que la cláusula WHERE no conoce el alias de la columna.

Por otro lado, podemos usar ALIAS en la cláusula ORDER BY ya que se evalúa después de la cláusula SELECT.

Usando un poco de todo basado en las siguientes reglas:

GROUP BY se procesa antes de el SELECT (no podemos usar el alias aquí)

HAVING se procesa antes de elSELECT (no podemos usar el alias aquí, pero podemos usar COUNT)

ORDEN BY se procesa después de elSELECT (podemos usar el alias aquí)

¡Diviértanse aprendiendo!