NO EN vs NO EXISTE

Siempre uso 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 NULLs, la NOT INversión necesitará hacer más trabajo (incluso si en NULLrealidad no hay s presentes en los datos) y la semántica de NOT INsi NULLlos s están presentes Es poco probable que sean los que desea de todos modos.

Cuando ninguno de los dos Products.ProductIDo lo [Order Details].ProductIDpermite , se tratará de manera idéntica a la siguiente consulta.NULLNOT IN

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.

Un error razonablemente común parece ser que las subconsultas correlacionadas siempre son "malas" en comparación con las uniones. Ciertamente pueden serlo cuando fuerzan un plan de bucles anidados (subconsulta evaluada fila por fila), pero este plan incluye un operador lógico anti-semiunión. Las antisemiuniones no se limitan a bucles anidados, sino que también pueden utilizar uniones hash o fusionadas (como en este ejemplo).

/*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].ProductIDes NULL-able 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 algún NULL ProductIds, es no devolver resultados. Consulte el carrete adicional anti semi unión y recuento de filas para verificar que se agregue al plan.

Si Products.ProductIDtambién se cambia para volverse NULL-able, 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) 

La razón de esto es que a NULL Products.ProductIdno debe devolverse en los resultados, excepto si la NOT INsubconsulta no arroja ningún resultado (es decir, la [Order Details]tabla está vacía). En cuyo caso debería hacerlo. En el plan de mis datos de muestra, esto se implementa agregando otra anti semi unión como se muestra a continuación.

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

Además, el hecho de que una sola NULLpueda reducir el recuento de filas a cero hace que la estimación de la cardinalidad sea muy difícil. Si SQL Server supone que esto sucederá pero en realidad no había NULLfilas 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 subproceso costoso. árbol por ejemplo .

Sin embargo , este no es el único plan de ejecución posible para una NOT INcolumna NULLcapaz. Este artículo muestra otro para una consulta a la AdventureWorks2008base de datos.

Para NOT INuna NOT NULLcolumna o NOT EXISTScontra una columna que acepta valores NULL o no, proporciona el siguiente plan.

Cuando la columna cambia a NULL-able, el NOT INplan ahora se ve así

Agrega un operador de unión interna adicional al plan. Este aparato se explica aquí . Todo está ahí para convertir la búsqueda de índice correlacionado único anterior Sales.SalesOrderDetail.ProductID = <correlated_product_id>en dos búsquedas por fila exterior. El adicional está encendido WHERE Sales.SalesOrderDetail.ProductID IS NULL.

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

Jun 17 '2012 20:06 Martin Smith