que - ¿Diferencia entre EXISTS e IN en SQL?

oracle exists vs in

Question

¿Cuál es la diferencia entre la cláusula EXISTS y IN en SQL?

¿Cuándo debemos usar EXISTS y cuándo debemos usar IN ?

Answer 1

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 un valor nulo, la Consulta 1 los encontrará, pero la Consulta 2 no puede encontrar los parámetros nulos.

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

Answer 2

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

¿Es eso un supuesto justo?

Edición : la razón por la que pregunto es que en muchos casos puede volver a escribir un SQL basado en IN para usar un EXISTS en su lugar, y viceversa, y para algunos motores de base de datos, el optimizador de consultas tratará a los dos de manera diferente.

Por ejemplo:

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

se puede reescribir 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

Así que mi pregunta sigue en pie, ¿el póster original se pregunta qué hace IN y EXISTS, y por lo tanto cómo usarlo o pregunta si reescribir un SQL usando IN para usar EXISTS en su lugar, o viceversa, será una buena idea?

Answer 3

Basado en el optimizador de reglas :

• 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.

Basado en el optimizador de costos :

• No hay diferencia.

Answer 4

Creo que esto tiene una respuesta directa. ¿Por qué no lo compruebas con las personas que desarrollaron esa función en sus sistemas?

Si usted es un desarrollador de MS SQL, aquí está la respuesta directamente de Microsoft.

IN :

Determina si un valor especificado coincide con algún valor en una subconsulta o una lista.

EXISTS :

Especifica una subconsulta para probar la existencia de filas.

Answer 5

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

• Cuando su consulta interna obtenga miles de filas, entonces EXIST sería la mejor opción
• Cuando su consulta interna obtenga pocas filas, entonces IN será más rápido

EXIST evalúa en verdadero o falso, pero EN compara valor múltiple. Cuando no sepa que el registro existe o no, debe elegir EXIST

Answer 6

Descubrí que usar la palabra clave 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

Answer 7

EXISTS es más rápido en rendimiento que en IN. Si la mayoría de los criterios de filtro están en la subconsulta, es mejor utilizar IN y si la mayoría de los criterios de filtro se encuentra en la consulta principal, entonces es mejor utilizar EXISTS.

Answer 8

La diferencia radica aquí:

select * 
from abcTable
where exists (select null)

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

select *
from abcTable
where abcTable_ID in (select null)

Pruébalo y observa la salida.

Answer 9

La palabra clave Exists evalúa verdadero o falso, pero la palabra clave IN compara todos los valores en la columna de subconsulta correspondiente. Otro Select 1 puede usarse con el comando Exists . Ejemplo:

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

Pero IN es menos eficiente por lo que Exists más rápido.

Answer 10

La palabra clave exists puede usarse de esa manera, pero en realidad está pensada para 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 cuando tienes sentencias condicionales, ya que exists pueden ser mucho más rápidas que count .

La entrada se utiliza mejor cuando tienes una lista estática para pasar:

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

Cuando tiene una tabla en una instrucción de entrada, tiene más sentido utilizar una join , pero sobre todo no debería importar. El optimizador de consultas debe devolver el mismo plan de cualquier manera. En algunas implementaciones (en su mayoría más antiguas, como Microsoft SQL Server 2000), las consultas siempre obtendrán un plan de unión anidado , mientras que las consultas de join usarán anidadas, merge o hash según corresponda. Las implementaciones más modernas son más inteligentes y pueden ajustar el plan incluso cuando se usa en.

Answer 11

La razón es que el operador EXISTS trabaja basándose en el principio "al menos encontrado". Devuelve verdadero y detiene la exploración de la tabla cuando se encuentra al menos una fila coincidente.

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

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

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

Answer 12

Mi entendimiento es que ambos deben ser iguales siempre que no estemos tratando con valores NULL.

La misma razón por la que 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 booleano vs comparador, para generar un booleano se deben comparar ambos valores y así es como funciona cualquier condición. Por lo tanto, no entiendo cómo IN y EXISTS se comportan de manera diferente.

Answer 13

Según mi conocimiento, cuando una subconsulta devuelve un valor NULL , toda la declaración se convierte en NULL . En esos casos estamos usando la palabra clave EXITS . Si queremos comparar valores particulares en subconsultas, entonces estamos usando la palabra clave IN .

Answer 14

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

Answer 15

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

Answer 16

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 conjunto de resultados de la subconsulta. Para realizar esta tarea, debe utilizar la palabra clave in .

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

Answer 17

Yo creo que,

• EXISTS es cuando necesita hacer coincidir los resultados de la consulta con otra subconsulta. Los resultados de la consulta # 1 deben recuperarse donde coincidan los resultados de la subconsulta. Tipo de unión. Por ejemplo, la tabla # 1 de clientes seleccionados que también ha realizado la tabla # 2 de pedidos.

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

Cuándo usar uno sobre el otro ... cuando sienta que se lee adecuadamente (se comunica mejor la intención).

Answer 18

EXISTS le dirá si una consulta devolvió 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 con varios, y puede usar valores literales, como este:

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

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

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

Answer 19

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

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

Answer 20

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

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

EXISTS admite varios tipos de relaciones, que no se pueden expresar 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 otra nota ...

Las supuestas diferencias de rendimiento y técnicas entre EXISTS y IN pueden deberse a implementaciones / limitaciones / errores de proveedores específicos, 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 estadísticas, la configuración de la base de datos y la versión del optimizador tienen un impacto en el plan de ejecución y, por lo tanto, en las métricas de rendimiento.