¿Diferencia entre EXISTS e IN en SQL?

¿Cuál es la diferencia entre las cláusulas EXISTS e IN en SQL?

¿Cuándo deberíamos usar EXISTS , y cuándo deberíamos usar IN ?

La palabra key exists se puede usar de esa manera, pero en realidad está pensada como una forma de evitar el conteo:

 --this statement needs to check the entire table select count(*) from [table] where ... --this statement is true as soon as one match is found exists ( select * from [table] where ... ) 

Esto es más útil donde tiene if las declaraciones condicionales, como exists puede ser mucho más rápido que count .

La input se usa mejor cuando tiene una list estática para aprobar:

  select * from [table] where [field] in (1, 2, 3) 

Cuando tienes una tabla en un enunciado, tiene más sentido usar un join , pero sobre todo no debería importar. El optimizador de consultas debe devolver el mismo plan de cualquier manera. En algunas implementaciones (principalmente más antiguas, como Microsoft SQL Server 2000) in consultas siempre obtendrá un plan de combinación nested , mientras que las consultas de join usarán nested, fusión o hash, según corresponda. Las implementaciones más modernas son más inteligentes y pueden ajustar el plan incluso cuando se utiliza.

EXISTS le dirá si una consulta arrojó algún resultado. p.ej:

 SELECT * FROM Orders o WHERE EXISTS ( SELECT * FROM Products p WHERE p.ProductNumber = o.ProductNumber) 

IN se usa para comparar un valor a varios, y puede usar valores literales, como este:

 SELECT * FROM Orders WHERE ProductNumber IN (1, 10, 100) 

También puede usar los resultados de la consulta con la cláusula IN , como esta:

 SELECT * FROM Orders WHERE ProductNumber IN ( SELECT ProductNumber FROM Products WHERE ProductInventoryQuantity > 0) 

Basado en el optimizador de reglas :

  • EXISTS es mucho más rápido que IN , cuando los resultados de la sub consulta son muy grandes.
  • IN es más rápido que EXISTS , cuando los resultados de la sub consulta son muy pequeños.

Basado en el optimizador de costos :

  • No hay diferencia.

Supongo que sabes lo que hacen, y por lo tanto se usan de manera diferente, así que voy a entender tu pregunta como: ¿Cuándo sería una buena idea reescribir el SQL para usar IN en lugar de EXISTS, o viceversa?

¿Es eso una suposition justa?


Editar : La razón por la que estoy preguntando es que en muchos casos puede reescribir un SQL basado en IN para usar un EXISTS en su lugar, y viceversa, y para algunos motores de database, el optimizador de consultas tratará los dos de manera diferente.

Por ejemplo:

 SELECT * FROM Customers WHERE EXISTS ( SELECT * FROM Orders WHERE Orders.CustomerID = Customers.ID ) 

puede ser reescrito a:

 SELECT * FROM Customers WHERE ID IN ( SELECT CustomerID FROM Orders ) 

o con una unión:

 SELECT Customers.* FROM Customers INNER JOIN Orders ON Customers.ID = Orders.CustomerID 

Entonces mi pregunta sigue en pie, ¿el poster original se pregunta qué hacen IN y EXISTS y, por lo tanto, cómo usarlo, o se pregunta si volver a escribir un SQL usando IN para usar EXISTS, o viceversa, será una buena idea?

  1. EXISTS es mucho más rápido que IN cuando los resultados de la subconsulta son muy grandes.
    IN es más rápido que EXISTS cuando los resultados de la subconsulta son muy pequeños.

     CREATE TABLE t1 (id INT, title VARCHAR(20), someIntCol INT) GO CREATE TABLE t2 (id INT, t1Id INT, someData VARCHAR(20)) GO INSERT INTO t1 SELECT 1, 'title 1', 5 UNION ALL SELECT 2, 'title 2', 5 UNION ALL SELECT 3, 'title 3', 5 UNION ALL SELECT 4, 'title 4', 5 UNION ALL SELECT null, 'title 5', 5 UNION ALL SELECT null, 'title 6', 5 INSERT INTO t2 SELECT 1, 1, 'data 1' UNION ALL SELECT 2, 1, 'data 2' UNION ALL SELECT 3, 2, 'data 3' UNION ALL SELECT 4, 3, 'data 4' UNION ALL SELECT 5, 3, 'data 5' UNION ALL SELECT 6, 3, 'data 6' UNION ALL SELECT 7, 4, 'data 7' UNION ALL SELECT 8, null, 'data 8' UNION ALL SELECT 9, 6, 'data 9' UNION ALL SELECT 10, 6, 'data 10' UNION ALL SELECT 11, 8, 'data 11' 
  2. Consulta 1

     SELECT FROM t1 WHERE not EXISTS (SELECT * FROM t2 WHERE t1.id = t2.t1id) 

    Consulta 2

     SELECT t1.* FROM t1 WHERE t1.id not in (SELECT t2.t1id FROM t2 ) 

    Si en t1 su id tiene valor nulo, la consulta 1 los encontrará, pero la consulta 2 no puede encontrar parameters nulos.

    Quiero decir, IN no puede comparar nada con nulo, por lo que no tiene ningún resultado para nulo, pero EXISTS puede comparar todo con nulo.

Si está utilizando el operador IN , el motor SQL escaneará todos los loggings obtenidos de la consulta interna. Por otro lado, si estamos utilizando EXISTS , el motor de SQL detendrá el process de escaneo tan pronto como encuentre una coincidencia.

La palabra key Exists evalúa verdadero o falso, pero la palabra key IN compara todos los valores en la columna correspondiente de la sub consulta. Otro, Select 1 se puede usar con el command Exists . Ejemplo:

 SELECT * FROM Temp1 where exists(select 1 from Temp2 where conditions...) 

Pero IN es less eficiente así que Exists más rápido.

Creo,

  • EXISTS es cuando necesita hacer coincidir los resultados de la consulta con otra subconsulta. Los resultados de Query # 1 deben recuperarse cuando los resultados de SubQuery coinciden. Tipo de unión. Por ejemplo, seleccionar la tabla de clientes n. ° 1 que también realizó la tabla de pedidos n. ° 2

  • IN es recuperar si el valor de una columna específica se encuentra IN una list (1,2,3,4,5). Ej. Seleccione clientes que se encuentran en los siguientes códigos postales, es decir, los valores zip_code se encuentran en la list (….).

Cuándo usar una sobre la otra … cuando sientes que se lee apropiadamente (Comunica la intención mejor).

La diferencia yace aquí:

 select * from abcTable where exists (select null) 

La consulta anterior devolverá todos los loggings, mientras que debajo de uno volverá vacío.

 select * from abcTable where abcTable_ID in (select null) 

Pruébalo y observa la salida.

IN solo admite relaciones de igualdad (o desigualdad cuando está precedido por NOT ).
Es un sinónimo de = any / = some , eg

 select * from t1 where x in (select x from t2) ; 

EXISTS admite types de relaciones variables, que no se pueden express utilizando IN , por ejemplo,

 select * from t1 where exists (select null from t2 where t2.x=t1.x and t2.y>t1.y and t2.z like '℅' || t1.z || '℅' ) ; 

Y en una nota diferente –

Las supuestas diferencias de performance y técnicas entre EXISTS y IN pueden ser el resultado de implementaciones / limitaciones / errores específicos del vendedor, pero muchas veces no son más que mitos creados debido a la falta de comprensión de las bases de datos internas.

La definición de las tablas, la precisión de las statistics, la configuration de la database y la versión del optimizador tienen impacto en el plan de ejecución y, por lo tanto, en las métricas de performance.

Según mi conocimiento, cuando una subconsulta devuelve un valor NULL , la statement completa pasa a ser NULL . En esos casos, estamos usando la palabra key EXITS . Si queremos comparar valores particulares en subconsultas, estamos usando la palabra key IN .

Cuál es más rápido depende de la cantidad de consultas obtenidas por la consulta interna:

  • Cuando su consulta interna recupere miles de filas, entonces EXISTA sería una mejor opción
  • Cuando su consulta interna recupera pocas filas, IN será más rápido

EXIST evalúa en verdadero o falso, pero IN compara el valor múltiple. Cuando no sabe que el logging existe o no, debe elegir EXIST

El motivo es que el operador EXISTS funciona según el principio "al less encontrado". Devuelve verdadero y deja de explorar la tabla una vez que se encuentra al less una fila coincidente.

Por otro lado, cuando el operador IN se combina con una subconsulta, MySQL debe procesar primero la subconsulta y luego utiliza el resultado de la subconsulta para procesar toda la consulta.

La regla general es que si la subconsulta contiene un gran volumen de datos, el operador EXISTS proporciona un mejor performance.

Sin embargo, la consulta que utiliza el operador IN funcionará más rápido si el set de resultados devuelto por la subconsulta es muy pequeño.

Entiendo que ambos deben ser iguales siempre que no estemos lidiando con valores NULL.

La misma razón por la cual la consulta no devuelve el valor para = NULL vs es NULL. http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

En cuanto al argumento boolean vs comparator, para generar un valor boolean se deben comparar ambos valores y así es como funciona cualquier condición if. Por lo tanto, no entiendo cómo IN y EXISTS se comportan de manera diferente.

Si una subconsulta devuelve más de un valor, es posible que deba ejecutar la consulta externa, si los valores dentro de la columna especificada en la condición coinciden con cualquier valor en el set de resultados de la subconsulta. Para realizar esta tarea, debe usar la palabra key in .

Puede usar una subconsulta para verificar si existe un set de loggings. Para esto, necesita usar la cláusula exists con una subconsulta. La palabra key exists siempre devuelve un valor verdadero o falso.

EXISTS tiene un performance más rápido que IN. Si la mayoría de los criterios de filter están en subconsulta, es mejor utilizar IN y si la mayoría de los criterios de filter están en la consulta principal, entonces es mejor utilizar EXISTS.

Creo que esto tiene una respuesta directa. ¿Por qué no lo comtestings de las personas que desarrollaron esa function en sus sistemas?

Si usted es un desarrollador de MS SQL, esta es la respuesta directa de Microsoft.

IN :

Determina si un valor especificado coincide con cualquier valor en una subconsulta o una list.

EXISTS :

Especifica una subconsulta para probar la existencia de filas.

Descubrí que usar la palabra key EXISTS a menudo es muy lento (eso es muy cierto en Microsoft Access). En su lugar, uso el operador de unión de esta manera: should-i-use-the-keyword-exists-in-sql

In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective pnetworkingicate is in the subquery, then use IN. If the selective pnetworkingicate is in the parent query, then use EXISTS.

https://docs.oracle.com/cd/B19306_01/server.102/b14211/sql_1016.htm#i28403

Si está utilizando el operador IN, el motor SQL escaneará todos los loggings obtenidos de la consulta interna. Por otro lado, si estamos utilizando EXISTS, el motor de SQL detendrá el process de escaneo tan pronto como encuentre una coincidencia.