Guinguette Marais Poitevin

Problema

¿Hay alguna diferencia entre utilizar el operador T-SQL IN o el operador EXISTSen una cláusula WHERE para filtrar valores específicos? ¿Hay una diferencia lógica, una diferencia de rendimiento o son exactamente lo mismo? ¿Y qué pasa con NOT IN y NOT EXISTS?

Solución

En este consejo investigaremos si hay alguna diferencia entre el operadorEXISTS y el IN. Esto puede ser lógico, es decir, que se comporten de forma diferente bajo ciertas circunstancias, o en cuanto al rendimiento, es decir, si el uso de un operador tiene un beneficio de rendimiento sobre el otro. Utilizaremos el DW 2017 de AdventureWorks para nuestras consultas de prueba.

SQL Server IN vs EXISTS

El operador IN se utiliza normalmente para filtrar una columna para una determinada lista de valores.Por ejemplo:

SELECT , ,FROM ..WHERE IN (1,2);

Esta consulta busca todas las subcategorías de productos que pertenecen a las categorías de productos Bicicletas y Categorías (ProductCategoryKey 1 y 2).

También puede utilizar el operador IN para buscar los valores en el conjunto de resultados de la asubconsulta:

SELECT , ,FROM ..WHERE IN ( SELECT FROM . WHERE = 'Bikes' );

Esta consulta devuelve todas las subcategorías vinculadas a la categoría Bicicletas.

La ventaja de utilizar una subconsulta es que la consulta está menos codificada; si la ProductCategoryKey cambia por alguna razón, la segunda consulta seguirá funcionando, mientras que la primera podría devolver resultados incorrectos. Es importante que la subconsulta devuelva exactamente una columna para que el operador IN funcione.

El operadorEXISTS no busca valores, sino que comprueba la existencia de filas. Normalmente, una subconsulta se utiliza junto con EXISTS. En realidad, no importa lo que devuelva la subconsulta, siempre que se devuelvan filas.

Esta consulta devolverá todas las filas de la tabla ProductSubcategory, porque la subconsulta interna devuelve filas (que no están relacionadas con la consulta externa en absoluto).

SELECT , ,FROM ..WHERE EXISTS ( SELECT 1/0 FROM . WHERE = 'Bikes' );

Como habrá observado, la subconsulta tiene 1/0 en la cláusula SELECT. En una consulta normal, esto devolvería un error de división por cero, pero dentro de una cláusula EXISTS está perfectamente bien, ya que esta división nunca se calcula. Esto demuestra que no es importante lo que devuelve la subconsulta, siempre y cuando se devuelvan filas.

Para utilizar EXISTS de una manera más significativa, puede utilizar una subconsulta correlacionada.En una subconsulta correlacionada, emparejamos valores de la consulta externa con valores de la (sub)consulta interna. De este modo, se comprueba si el valor de la consulta externa existe en la tabla utilizada en la consulta interna. Por ejemplo, si queremos devolver una lista de todos los empleados que han realizado una venta, podemos escribir la siguiente consulta:

SELECT , , ,FROM .. eWHERE EXISTS ( SELECT 1 FROM dbo. f WHERE e. = f. );

En la cláusula WHERE dentro de la subconsulta EXISTS, correlacionamos la clave de empleado de la tabla externa – DimEmployee – con la clave de empleado de la tabla interna – FactResellerSales. Si la clave de empleado existe en ambas tablas, se devuelve una fila y EXISTS devolverá true. Si la clave del empleado no se encuentra en FactResellerSales,EXISTS devuelve false y el empleado se omite de los resultados:

Podemos implementar la misma lógica utilizando el operador IN:

SELECT , , ,FROM .. eWHERE IN ( SELECT FROM dbo. f );

Ambas consultas devuelven el mismo conjunto de resultados, pero quizá haya una diferencia de rendimiento subyacente. Comparemos los planes de ejecución.

Este es el plan para EXISTS:

Este es el plan para IN:

Son exactamente iguales. Al ejecutar ambas consultas al mismo tiempo, se puede ver que se les asigna el mismo coste:

El plan de ejecución superior es para EXISTS, el inferior para IN.

Echemos un vistazo a las estadísticas de IO (puede mostrarlas ejecutando la sentencia SET STATISTICS IO ON). De nuevo, todo es exactamente igual:

Así que no hay ninguna diferencia de rendimiento que podamos probar y ambos devuelven los mismos conjuntos de resultados. ¿Cuándo elegirías usar uno u otro? Aquí hay algunas pautas:

• Si tiene una pequeña lista de valores estáticos (y los valores no están presentes en alguna tabla), es preferible el operador IN.
• Si necesita comprobar la existencia de valores en otra tabla, es preferible el operador EXISTS, ya que demuestra claramente la intención de la consulta.
• Si necesita comprobar más de una sola columna, puede utilizar EXISTS, ya que el operador IN sólo le permite comprobar una sola columna.

Ilustremos el último punto con un ejemplo. En el datawarehouse de AdventureWorks, tenemos una dimensión Empleado. Algunos empleados gestionan un territorio de ventas específico:



Ahora bien, es posible que un vendedor también realice ventas en otros territorios.Por ejemplo, Michael Blythe -responsable de la región noreste- ha vendido en4 regiones distintas:



Supongamos que ahora sólo queremos encontrar los importes de las ventas de los responsables de los territorios de venta, pero sólo para su propia región. Una posible consulta podría ser:

SELECT f. ,f. ,SUM()FROM . fWHERE EXISTS ( SELECT 1 FROM . e WHERE f. = e. AND f. = e. AND e. <> 11 -- the NA region )GROUP BY f. ,f.;

El resultado es el siguiente:



Dentro de la cláusula EXISTS, recuperamos los gestores de territorios de ventas filtrando todos los empleados vinculados a la región NA. En la consulta externa, obtenemos todas las ventas por territorio de ventas y empleado, donde el empleado y el territorio se encuentran en la consulta interna. Como puede ver, EXISTS nos permite comprobar fácilmente en múltiples columnas, lo que no es posible con IN.

SQL Server NOT IN vs NOT EXISTS

Al prefijar los operadores con el operador NOT, negamos la salida booleana de esos operadores. Usando NOT IN, por ejemplo, devolverá todas las filas con un valor que no se puede encontrar en una lista.



Hay un caso especial: cuando los valores NULL entran en escena. Si un valor NULL está presente en la lista, ¡el conjunto de resultados está vacío!



Esto significa que NOT IN puede devolver resultados inesperados si de repente aparece un valor NULL en el conjunto de resultados de la subconsulta. NOT EXISTS no tiene este problema, ya que no importa lo que se devuelva. Si se devuelve un conjunto de resultados vacío, NOT EXISTS lo negará, lo que significa que el registro actual no se filtrará:



La consulta anterior devuelve todos los empleados que no han realizado una venta. Lógicamente, NOT IN y NOT EXISTS son lo mismo, es decir, devuelven los mismos conjuntos de resultados, siempre que no haya NULLs. ¿Hay alguna diferencia de rendimiento? De nuevo, ambos planes de consulta son iguales:



Lo mismo ocurre con las estadísticas IO:



Sin embargo, hay un problema. El EmployeeKey no es nulo en FactResellerSales.Como se ha demostrado antes, NOT IN puede tener problemas cuando hay NULLs involucrados. Si cambiamosEmployeeKey para que sea nulo, obtendremos los siguientes planes de ejecución:



¡Qué diferencia esta vez! Como SQL Server tiene que tener en cuenta los valores NULL, el plan de ejecución cambia. Lo mismo se puede ver en las estadísticas de IO:



Ahora sí que hay una diferencia de rendimiento entre NOT IN y NOT EXISTS.¿Cuándo usar qué operador? Algunas pautas:

• Se pueden aplicar las mismas directrices que para IN y EXISTS. Para comprobar una lista estática pequeña, es preferible utilizar NOT IN. ¿Comprobando la existencia en otra tabla? NOT EXISTS es la mejor opción. Comprobando contra múltiples columnas, de nuevoNOT EXISTS.
• Si una de las columnas es anulable, es preferible NOT EXISTS.

Usando Joins en lugar de IN o EXISTS

La misma lógica se puede implementar con joins también. Una alternativa para IN y EXISTS es unINNER JOIN, mientras que unLEFT OUTER JOIN con una cláusula WHERE que compruebe los valores NULL puede utilizarse como alternativa para NOT IN y NOT EXISTS. La razón por la que no se incluyen en este consejo -aunque puedan devolver exactamente el mismo conjunto de resultados y plan de ejecución- es porque la intención es diferente. Con IN y EXISTS, se comprueba la existencia de valores en otro conjunto de registros. Con los joins se combinan los conjuntos de resultados, lo que significa que se tiene acceso a todas las columnas de la otra tabla. La comprobación de la existencia es más bien un «efecto secundario». Cuando se utiliza (NOT) IN y (NOT) EXISTS, queda muy claro cuál es la intención de la consulta. Las uniones, por otro lado, pueden tener múltiples propósitos.

Usando un INNER JOIN, también puede tener múltiples filas devueltas para el mismo valor si hay múltiples coincidencias en la segunda tabla. Si desea comprobar la existenciay si un valor existe necesita una columna de la otra tabla, se prefieren los joins.

Siguientes pasos

• Puede encontrar más consejos de T-SQL en estavista.
• La veterana MVP Gail Shaw tiene una buena serie sobre EXISTS vs IN vs JOINS. Si te interesa comparar EXISTS/IN frente a los JOINS, puedes leer los siguientes posts del blog:
  • IN vs INNER JOIN
  • LEFT OUTER JOIN vs NOT EXISTS
• Consejos de Join de SQL Server
• Consejo:Ejemplo de Join de SQL Server

Última actualización: 2019-05-13





.

Acerca del autor

Koen Verbeeck es un profesional del BI, especializado en la pila de BI de Microsoft con un amor particular por SSIS.
Ver todos mis consejos

Recursos relacionados

• Más consejos para desarrolladores de bases de datos…