sql - usar - porque no está indizado por texto completo

¿Cómo funciona el predicado IN en SQL?

Question

Después de preparar una respuesta para esta pregunta , descubrí que no podía verificar mi respuesta.

En mi primer trabajo de programación, me dijeron que se ejecuta una consulta dentro del predicado IN () para cada fila contenida en la consulta principal, y por lo tanto, se debe evitar el uso de IN .

Por ejemplo, dada la consulta:

SELECT count(*) FROM Table1 WHERE Table1Id NOT IN (
SELECT Table1Id FROM Table2 WHERE id_user = 1)

Table1 Rows | # of "IN" executions ---------------------------------- 10 | 10 100 | 100 1000 | 1000 10000 | 10000

¿Es esto correcto? ¿Cómo funciona realmente el predicado IN ?

Answer 1

Depende del optimizador. Compruebe el plan de consulta exacto para cada consulta en particular para ver cómo el RDBMS realmente lo ejecutará.

En Oracle eso sería:

EXPLAIN PLAN FOR «your query»

En MySQL o PostgreSQL

EXPLAIN «your query»

Answer 2

Dependerá completamente de la base de datos que esté utilizando y de la consulta exacta.

Los optimizadores de consultas son muy inteligentes a veces: en su consulta de muestra, espero que las mejores bases de datos puedan usar el mismo tipo de técnicas que con una combinación. Las bases de datos más ingenuas pueden ejecutar la misma consulta muchas veces.

Answer 3

Esto depende del RDBMS en cuestión.

Ver análisis detallado aquí:

• MySQL, parte 1
• MySQL, parte 2
• servidor SQL
• Oracle
• PostgreSQL

En breve:

1. MySQL optimizará la consulta a esto:

   SELECT  COUNT(*)
   FROM    Table1 t1
   WHERE   NOT EXISTS
           (
           SELECT  1
           FROM    Table2 t2
           WHERE   t2.id_user = 1
                   AND t2.Table1ID = t1.Table2ID
           )

   y ejecute la subconsulta interna en un bucle, usando la búsqueda de índice cada vez.

   • SQL Server usará MERGE ANTI JOIN .

   La subconsulta interna no se "ejecutará" en un sentido común de palabra; en su lugar, los resultados tanto de la consulta como de la subconsulta se obtendrán al mismo tiempo.

   Vea el enlace de arriba para una explicación detallada.

   • Oracle usará HASH ANTI JOIN .

   La subconsulta interna se ejecutará una vez y se generará una tabla hash a partir del conjunto de resultados.

   Los valores de la consulta externa se buscarán en la tabla hash.

   • PostgreSQL usará NOT (HASHED SUBPLAN) .

   Al igual que Oracle .

Tenga en cuenta que volver a escribir la consulta como esta:

SELECT  (
        SELECT  COUNT(*)
        FROM    Table1
        ) - 
        (
        SELECT  COUNT(*)
        FROM    Table2 t2
        WHERE   (t2.id_user, t2.Table1ID) IN
                (
                SELECT  1, Table1ID
                FROM    Table1
                )
        )

mejorará en gran medida el rendimiento en los cuatro sistemas.

Answer 4

La advertencia que recibió sobre las subconsultas que se ejecutan para cada fila es verdadera, para las subconsultas correlacionadas.

SELECT COUNT(*) FROM Table1 a 
WHERE a.Table1id NOT IN (
  SELECT b.Table1Id FROM Table2 b WHERE b.id_user = a.id_user
);

Tenga en cuenta que la id_user consulta hace referencia a la columna id_user de la consulta externa. El valor de id_user en cada fila de Table1 puede ser diferente. Entonces, el resultado de la subconsulta probablemente será diferente, dependiendo de la fila actual en la consulta externa. El RDBMS debe ejecutar la subconsulta muchas veces, una para cada fila en la consulta externa.

El ejemplo que probó es una subconsulta no correlacionada . La mayoría de los optimizadores de RDBMS modernos que valen la pena deberían poder decir cuándo el resultado de la subconsulta no depende de los valores en cada fila de la consulta externa. En ese caso, el RDBMS ejecuta la subconsulta una sola vez, guarda su resultado en caché y lo usa repetidamente para el predicado en la consulta externa.

PD: en SQL, IN() se llama un "predicado", no una declaración. Un predicado es una parte del lenguaje que se evalúa como verdadero o falso, pero no necesariamente se puede ejecutar de forma independiente como una declaración. Es decir, no puede ejecutar esto como una consulta SQL: "2 IN (1,2,3);" Aunque este es un predicado válido, no es una declaración válida.

Answer 5

La mayoría de los motores SQL hoy en día casi siempre crearán el mismo plan de ejecución para LEFT JOIN, NOT IN y NOT EXISTS

Yo diría que mira tu plan de ejecución y averigua :-)

Además, si tiene valores NULL para la columna Table1Id, no obtendrá ningún dato

Answer 6

Realmente no. Pero es mantequilla escribir tales consultas usando JOIN

Answer 7

Sí, pero la ejecución se detiene tan pronto como el procesador de consultas "encuentra" el valor que está buscando ... Entonces, si, por ejemplo, la primera fila en el exterior selecciona Table1Id = 32, y si Table2 tiene un registro con un TableId = 32, entonces tan pronto como la subconsulta encuentre la fila en Table2 donde TableId = 32, se detiene ...