sql - advanced - NO EN vs NO EXISTE

advanced sql

Question

¿Cuál de estas consultas es la más rápida?

NO EXISTE:

SELECT ProductID, ProductName 
FROM Northwind..Products p
WHERE NOT EXISTS (
    SELECT 1 
    FROM Northwind..[Order Details] od 
    WHERE p.ProductId = od.ProductId)

O NO EN:

SELECT ProductID, ProductName 
FROM Northwind..Products p
WHERE p.ProductID NOT IN (
    SELECT ProductID 
    FROM Northwind..[Order Details])

El plan de ejecución de la consulta dice que ambos hacen lo mismo. Si ese es el caso, ¿cuál es la forma recomendada?

Esto se basa en la base de datos NorthWind.

[Editar]

Acabo de encontrar este artículo útil: http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

Creo que me quedo con NO EXISTS.

Answer 1

Depende..

SELECT x.col
FROM big_table x
WHERE x.key IN( SELECT key FROM really_big_table );

no sería relativamente lento, no hay mucho para limitar el tamaño de lo que verifica la consulta para ver si la clave está en. EXISTS sería preferible en este caso.

Pero, dependiendo del optimizador del DBMS, esto no podría ser diferente.

Como ejemplo de cuando EXISTS es mejor

SELECT x.col
FROM big_table x
WHERE EXISTS( SELECT key FROM really_big_table WHERE key = x.key);
  AND id = very_limiting_criteria

Answer 2

En realidad, creo que este sería el más rápido:

SELECT ProductID, ProductName 
    FROM Northwind..Products p  
          outer join Northwind..[Order Details] od on p.ProductId = od.ProductId)
WHERE od.ProductId is null

Answer 3

En su ejemplo específico, son iguales, porque el optimizador ha descubierto que lo que está tratando de hacer es el mismo en ambos ejemplos. Pero es posible que en ejemplos no triviales el optimizador no pueda hacer esto, y en ese caso hay razones para preferir uno a otro en la ocasión.

Debería preferir NOT IN ENTRAR si está probando varias filas en su selección externa. La subconsulta dentro de la instrucción NOT IN puede evaluarse al comienzo de la ejecución, y la tabla temporal puede compararse con cada valor en la selección externa, en lugar de volver a ejecutar la subselección cada vez que sea necesario con la instrucción NOT EXISTS .

Si la subconsulta debe estar correlacionada con la selección externa, entonces puede que sea preferible NOT EXISTS , ya que el optimizador puede descubrir una simplificación que impide la creación de tablas temporales para realizar la misma función.

Answer 4

Estaba usando

SELECT * from TABLE1 WHERE Col1 NOT IN (SELECT Col1 FROM TABLE2)

y descubrió que estaba dando resultados incorrectos (por error quiero decir que no hay resultados). Como había un NULL en TABLE2.Col1.

Mientras se cambia la consulta a

SELECT * from TABLE1 T1 WHERE NOT EXISTS (SELECT Col1 FROM TABLE2 T2 WHERE T1.Col1 = T2.Col2)

Me dio los resultados correctos.

Desde entonces he empezado a usar NOT EXISTS en todas partes.

Answer 5

Si el optimizador dice que son iguales, entonces considera el factor humano. Prefiero ver NO EXISTE :)

Answer 6

Si el planificador de ejecución dice que son lo mismo, son lo mismo. Use el que sea que haga más obvia su intención: en este caso, el segundo.

Answer 7

Siempre por defecto NOT EXISTS .

Los planes de ejecución pueden ser los mismos en este momento, pero si alguna de las columnas se modifica en el futuro para permitir NULL s, la versión NOT IN tendrá que hacer más trabajo (incluso si no hay NULL s realmente en los datos) y la semántica de NOT IN si los NULL están presentes es poco probable que sean los que desee de todos modos.

Cuando ni Products.ProductID o [Order Details].ProductID permiten NULL s, NOT IN se tratará de forma idéntica a la siguiente consulta.

SELECT ProductID,
       ProductName
FROM   Products p
WHERE  NOT EXISTS (SELECT *
                   FROM   [Order Details] od
                   WHERE  p.ProductId = od.ProductId) 

El plan exacto puede variar, pero para los datos de mi ejemplo, obtengo lo siguiente.

Una idea errónea bastante común parece ser que las subconsultas correlacionadas son siempre "malas" en comparación con las uniones. Ciertamente, pueden ser cuando fuerzan un plan de bucles anidados (sub consulta que se evalúa fila por fila) pero este plan incluye un operador lógico anti semi-unido. Las antisemergencias no están restringidas a bucles anidados, pero pueden usar combinaciones hash o fusionadas (como en este ejemplo) también.

/*Not valid syntax but better reflects the plan*/ 
SELECT p.ProductID,
       p.ProductName
FROM   Products p
       LEFT ANTI SEMI JOIN [Order Details] od
         ON p.ProductId = od.ProductId 

Si [Order Details].ProductID es NULL la consulta se convierte en

SELECT ProductID,
       ProductName
FROM   Products p
WHERE  NOT EXISTS (SELECT *
                   FROM   [Order Details] od
                   WHERE  p.ProductId = od.ProductId)
       AND NOT EXISTS (SELECT *
                       FROM   [Order Details]
                       WHERE  ProductId IS NULL) 

La razón de esto es que la semántica correcta si [Order Details] contiene cualquier ProductId NULL es no devolver ningún resultado. Consulte el carrete adicional anti semi join y row count para verificar que se haya agregado al plan.

Si Products.ProductID también se cambia para convertirse en NULL la consulta se convierte en

SELECT ProductID,
       ProductName
FROM   Products p
WHERE  NOT EXISTS (SELECT *
                   FROM   [Order Details] od
                   WHERE  p.ProductId = od.ProductId)
       AND NOT EXISTS (SELECT *
                       FROM   [Order Details]
                       WHERE  ProductId IS NULL)
       AND NOT EXISTS (SELECT *
                       FROM   (SELECT TOP 1 *
                               FROM   [Order Details]) S
                       WHERE  p.ProductID IS NULL) 

El motivo es que no se debe devolver un NULL Products.ProductId en los resultados, excepto si la sub consulta NOT IN no devolviera ningún resultado (es decir, la tabla [Order Details] está vacía). En cuyo caso debería. En el plan para mis datos de muestra, esto se implementa agregando otra combinación semi como se muestra a continuación.

El efecto de esto se muestra en la publicación del blog ya enlazada por Buckley . En el ejemplo, el número de lecturas lógicas aumenta de alrededor de 400 a 500,000.

Además, el hecho de que un solo NULL pueda reducir el recuento de filas a cero hace que la estimación de cardinalidad sea muy difícil. Si SQL Server supone que esto sucederá, pero en realidad no hubo filas NULL en los datos, el resto del plan de ejecución puede ser catastróficamente peor, si esto es solo parte de una consulta más grande, con bucles anidados inapropiados que causan la ejecución repetida de un costoso sub árbol por ejemplo .

Sin embargo, este no es el único plan de ejecución posible para un NOT IN en una columna NULL -able. Este artículo muestra otro para una consulta en la base de datos AdventureWorks2008 .

Para el NOT IN en una columna NOT NULL o el NOT EXISTS contra una columna que no acepta nulos o nullable da el siguiente plan.

Cuando la columna cambia a NULL ver el plan NOT IN ahora

Agrega un operador de unión interna adicional al plan. Este aparato se explica aquí . Es todo lo que hay para convertir la búsqueda de índice correlacionada única anterior en Sales.SalesOrderDetail.ProductID = <correlated_product_id> en dos búsquedas por fila exterior. El adicional está en WHERE Sales.SalesOrderDetail.ProductID IS NULL .

Como esto se encuentra bajo una combinación anti semi, si esa devuelve alguna fila, la segunda búsqueda no ocurrirá. Sin embargo, si Sales.SalesOrderDetail no contiene ningún Sales.SalesOrderDetail NULL , duplicará el número de operaciones de búsqueda requeridas.

Answer 8

Son muy similares pero no son lo mismo.

En términos de eficiencia, he encontrado que la unión izquierda es una declaración nula más eficiente (cuando hay que seleccionar una gran cantidad de filas)

Answer 9

También tenga en cuenta que NOT IN no es equivalente a NOT EXISTS cuando se trata de null.

Este post lo explica muy bien.

http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

Cuando la subconsulta devuelve incluso un nulo, NOT IN no coincidirá con ninguna fila.

El motivo de esto se puede encontrar mirando los detalles de lo que realmente significa la operación NO ENTRAR.

Digamos, para fines de ilustración que hay 4 filas en la tabla llamada t, hay una columna llamada ID con valores 1..4

WHERE SomeValue NOT IN (SELECT AVal FROM t)

es equivalente a

WHERE SomeValue != (SELECT AVal FROM t WHERE ID=1)
AND SomeValue != (SELECT AVal FROM t WHERE ID=2)
AND SomeValue != (SELECT AVal FROM t WHERE ID=3)
AND SomeValue != (SELECT AVal FROM t WHERE ID=4)

Digamos además que AVal es NULL donde ID = 4. Por lo tanto, = la comparación devuelve DESCONOCIDO. La tabla de verdad lógica para AND indica que DESCONOCIDO y VERDADERO es DESCONOCIDO, DESCONOCIDO y FALSO es FALSO. No hay ningún valor que pueda ser AND''con DESCONOCIDO para producir el resultado VERDADERO

Por lo tanto, si alguna fila de esa subconsulta devuelve NULL, el operador NOT IN completo se evaluará en FALSE o NULL y no se devolverán registros

Answer 10

Tengo una tabla que tiene aproximadamente 120,000 registros y necesito seleccionar solo aquellos que no existen (combinados con una columna varchar) en otras cuatro tablas con un número de filas de aproximadamente 1500, 4000, 40000, 200. Todas las tablas involucradas tienen un índice único en la columna Varchar cuestión.

NOT IN duró unos 10 minutos, NOT EXISTS tardó 4 segundos.

Tengo una consulta recursiva que podría haber tenido una sección no sintonizada que podría haber contribuido a los 10 minutos, pero la otra opción que toma 4 segundos explica, al menos para mí que NOT EXISTS es mucho mejor o al menos que IN y EXISTS no son exactamente lo mismo Y siempre vale la pena un cheque antes de seguir adelante con el código.