left - using sql

¿Cuál es la diferencia entre NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?

Question

Me parece que puede hacer lo mismo en una consulta SQL utilizando NOT EXISTS, NOT IN o LEFT JOIN WHERE IS NULL. Por ejemplo:

SELECT a FROM table1 WHERE a NOT IN (SELECT a FROM table2)

SELECT a FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.a = table2.a)

SELECT a FROM table1 LEFT JOIN table2 ON table1.a = table2.a WHERE table1.a IS NULL

No estoy seguro si obtuve toda la sintaxis correcta, pero estas son las técnicas generales que he visto. ¿Por qué elegiría usar uno sobre el otro? ¿El rendimiento difiere ...? ¿Cuál de estos es el más rápido / más eficiente? (Si depende de la implementación, ¿cuándo usaría cada uno?)

Answer 1

Asumiendo que está evitando nulos, todos son formas de escribir un anti-join usando Standard SQL.

Una omisión obvia es el equivalente usando EXCEPT :

SELECT a FROM table1
EXCEPT
SELECT a FROM table2

Tenga en cuenta que en Oracle necesita usar el operador MINUS (podría decirse que es un nombre mejor):

SELECT a FROM table1
MINUS
SELECT a FROM table2

Hablando de sintaxis patentada, también puede haber equivalentes no estándar que valga la pena investigar dependiendo del producto que esté utilizando, por ejemplo OUTER APPLY en SQL Server (algo así como):

SELECT t1.a
  FROM table1 t1
       OUTER APPLY 
       (
        SELECT t2.a
          FROM table2 t2
         WHERE t2.a = t1.a
       ) AS dt1
 WHERE dt1.a IS NULL;

Answer 2

Cuando necesite insertar datos en la tabla con clave primaria de múltiples campos, considere que será mucho más rápido (lo intenté en Access pero creo en cualquier base de datos) para no verificar que "no existan registros con ''tales'' valores en la tabla", - simplemente inserte en la tabla, y los registros sobrantes (con la tecla) no se insertarán dos veces.

Answer 3

La perspectiva de rendimiento siempre evita usar palabras clave inversas como NOT IN, NOT EXISTS, ... Porque para verificar los ítems inversos, DBMS necesita ejecutarse a través de todas las opciones disponibles y descartar la selección inversa.

Answer 4

Si la base de datos es buena para optimizar la consulta, las dos primeras se transformarán en algo cercano a la tercera.

Para situaciones simples como las de su pregunta, debe haber poca o ninguna diferencia, ya que todas se ejecutarán como uniones. En consultas más complejas, la base de datos puede no ser capaz de hacer una combinación de las consultas not in y not exists . En ese caso, las consultas serán mucho más lentas. Por otro lado, una unión también puede funcionar mal si no hay un índice que se pueda usar, así que solo porque uses una unión no significa que estés a salvo. Tendría que examinar el plan de ejecución de la consulta para saber si puede haber algún problema de rendimiento.

Answer 5

• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server

• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: PostgreSQL

• NO EN vs. NO EXISTE vs. IZQUIERDA UNIRSE / ES NULO: Oracle

• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL

En una palabra:

NOT IN es un poco diferente: nunca coincide si solo hay un NULL en la lista.

• En MySQL , NOT EXISTS es un poco menos eficiente

• En SQL Server , LEFT JOIN / IS NULL es menos eficiente

• En PostgreSQL , NOT IN es menos eficiente

• En Oracle , los tres métodos son iguales.