In vs. Exists


He escrito anteriormente en este artículo sobre el uso de NOT IN y NOT EXISTS, mencionando que se trataba de cláusulas diferentes y que, por lo tanto, podían generar diferentes resultados. También mencioné que en su forma natural, IN y EXISTS suponen semánticamente operaciones distintas con diferentes vías de ejecución la mayoría de los casos, pero que pueden implementarse de forma que los resultados sean idénticos y permitir así el sustituir IN por EXISTS y viceversa.

Antes de desglosar la ejecución de IN y EXISTS para una su optimización, menciono la forma de implementarse.                  

IN

SELECT campo1, campo2, …, campoN
FROM   tabla1
WHERE  campoX IN (–subconsulta
                  SELECT campo1
                  FROM   tabla2
                  WHERE  condicion);

Cierto.

Esta consulta podría implementarse como una natural join. Se trata de un ejemplo. Oracle en ambos casos, tratándose de subconsultas simples, las interpreta como joins y resuelven el mismo plan de ejecución.

SELECT t1.campo1, t1.campo2, …, t1.campoN
FROM   tabla1 t1, tabla2 t2
WHERE  t1.campoX = t2.campo1
  AND  condicion;
  

Es decir, ambas consultas combinan los resultados de Tabla1 y Tabla2, unidos por las columnas t1.campoX y t2.campo1 de modo que visualicen únicamente los resultados de la tabla1 que cumplen la condición de combinación.

La interpretación de la consulta, realizada con IN, es la siguiente:

Selecciona el campo1, campo2, … hasta campoN
de la tabla tabla1
unicamente para las filas cuyo campoX se encuentre en la siguiente lista de valores campo1 de la tabla2, que cumplan con la condición condicion.

La forma de resolver esta petición, para el servidor, es consultar la tabla2 en la subconsulta y delimitar después qué campos de la tabla1 cumplen la condición de join.

EXISTS

SELECT campo1, campo2, …, campoN
FROM   tabla1
WHERE  EXISTS  (–subconsulta
                  SELECT NULL
                  FROM   tabla2
                  WHERE  t1.campoX=t2.campo1
                    AND  condicion);

No existe en esta forma una comparación de campos, salvo en la subconsulta. Ésta devuelve NULL (ni siquiera tendría que devolver un valor concreto para ser válida) y su interpretación semántica es la siguiente:

Selecciona el campo1, campo2, … hasta campoN
de la tabla tabla1
unicamente cuando la subconsulta devuelva un registro (aunque éste sea NULL).
La subconsulta, por tanto, devolverá un registro para aquellos elementos de la tabla2 cuyo campo1 sea igual que el campoX de la tabla principal.

Por lo que Oracle entiende que la consulta más idónea a resolverse antes es la principal, resolver los valores de campoX, y posteriormente compararlos con tabla2.

Es decir, en la ejecución con EXISTS, Oracle entiende que la consulta principal debe resolverse primero, y la subconsulta después. Justo al contrario que IN, lo que puede producir diferentes planes de ejecución.

Generalmente, IN resulta más ventajoso cuando la ejecución de la subconsulta tiene poco coste respecto a la principal, y EXISTS cuando sucede al revés.

(…continuará…)

Comments

  1. Anónimo
    • 8 octubre, 2005
    • 11:51 pm
    • Reply

    Muchas gracias por tus post, estoy aprendiendo mucho!!

  2. Javier Morales
    • 9 octubre, 2005
    • 7:19 pm
    • Reply

    Gracias a ti, por leerme!
    Si quieres que publique algo concreto, siéntete libre en pedir (muchas veces no sé qué publicar!) 🙂

    ¡Gracias!

  3. Anónimo
    • 27 septiembre, 2006
    • 3:58 pm
    • Reply

    Hola Javier, quería felicitarte por tu blog tanto por su contenido, como con la sencillez con la que expones tus artículos.

    Un Saludo.

  4. Anónimo
    • 10 abril, 2007
    • 12:05 pm
    • Reply

    Hola tengo la siguiente consulta en SQL SERVER 2000 desde VISUAL .NET 2005

    Dim cSql As String = «Select top 40 apell1,apell2,nom,nhc From PACIENTE WHERE apell1 NOT IN (SELECT TOP 0 apell1 From PACIENTE Order By NHC,apell1) Order By NHC»

    Para que me muestre sólo 40 registros , y que no los repita
    ESTO FUNCIONA EN SQL-SERVER 2000

    e intento hacerlo para ORACLE 9i

    Dim cSql As String = «Select apell1,apell2,nom,nhc From PACIENTE WHERE rownum =40 and apell1 NOT IN (ROWNUM=0 apell1 From PACIENTE Order By NHC,apell1) Order By NHC»

    PERO ME DA ERROR EN LA CONSULTA DE ORACLE
    HE PROBADO ESTO Y SI FUNCIONA

    Dim cSql As String = «Select apell1,apell2,nom,nhc From PACIENTE WHERE rownum =40 Order By NHC»

    ¿Como podría añadir NOT IN para que no me repita los 40 registros y me muestre los 40 siguientes de la tabla ?

    MUCHAS GRACIAS

  5. Javier Morales
    • 10 abril, 2007
    • 3:00 pm
    • Reply

    Hola Anónimo:
    Entiendo que lo que dices que te ha funcionado es:

    Select apell1,apell2,nom,nhc
    From PACIENTE
    WHERE rownum <=40
    Order By NHC;

    ya que rownum=40 no retornaría ninguna fila.

    Bien. Ésto devuelve las primeras 40 filas que Oracle quiere. En tu caso, para que devuelva las primeras (por ejemplo, por orden alfabético), la consulta podría ser ésta:

    Select apell1,apell2,nom,nhc
    From (select * from PACIENTE order by apell1) p
    WHERE rownum <=40
    Order By NHC;

    No obstante, para sacar los siguientes cuarenta tendrías que hacer algo tan poco profesional como ésto:

    Select apell1,apell2,nom,nhc
    From (select paciente.*, rownum numero_fila from PACIENTE order by apell1) p
    WHERE rownum <=40
    AND numero_fila not between 1 and 40
    Order By NHC;

    lo cual es peor aun.

    Mi recomendación es que te crees tres procedimientos dentro de un paquete: uno que abra el cursor de pacientes, otro que haga fetch de 40 filas (dejando el cursor abierto para que una nueva invocación retorne las 40 siguientes, y así sucesivamente), y otro que te cierre el cursor.

    Desde .NET gestionarás los resultados de forma igualmente transparente y mejorarás muchísimo en rendimiento y «limpieza» de código (=mejor mantenimiento, etc).

    Suerte y un saludo!
    Javier Morales

  6. Anónimo
    • 15 junio, 2007
    • 5:32 pm
    • Reply

    Hola Javier… primero que nada, felicidades por tu blog, esta muy completo.

    Quisiera saber si me puedes ayudar con esta sentencia:
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Emp_Articulo]’) and OBJECTPROPERTY(id, N’IsForeignKey’) = 1)
    ALTER TABLE [dbo].[Articulo] DROP CONSTRAINT Emp_Articulo
    GO
    Necesito pasarla de sql server 2000 a oracle 9i…. pero desconosco las funciones y sintaxis de object_id y OBJECTPROPERTY… asi como tmb el uso del EXIST en oracle.

    Gracias!!

  7. Javier Morales
    • 19 junio, 2007
    • 8:57 pm
    • Reply

    Hola Anónimo,
    entiendo que lo que quieres hacer es deshabilitar una constraint de foreign key sobre una columna únicamente si esta existe. ¿es correcto?
    Pues algo tan fácil (y distinto) como ejecutar ésto:

    begin
    for x in (select c.constraint_name
    from all_cons_columns cc, all_constraints c
    where c.constraint_name=cc.constraint_name
    and c.owner=’USUARIO ‘
    and c.table_name=’DBO’
    and cc.column_name=’EMP_ARTICULO’
    and c.constraint_type=’R’)
    begin
    execute immediate ‘alter table DBO drop constraint ‘||x.constraint_name;
    end;
    end;
    /

    aunque, personalmente, si se trata de desactivar las constraints, ejecutaría simplemente:

    alter table DBO.EMP_ARTICULO disable constraint nombre_constraint;

    Un saludo,
    Javier

  8. Cristian.
    • 12 julio, 2007
    • 4:05 pm
    • Reply

    Muy buen blog, me ha quedado completamente clara la diferencia entre el in y el exists.

  9. Anónimo
    • 12 octubre, 2007
    • 10:28 pm
    • Reply

    Hola me parece excelente tu blog y quiero felicitarte, tengo una duda ojala puedas ayudarme tengo una consulta usando IN y quisiera saber cual seria su equivalente en exists

    Select cliente,cuenta,deposito,fecha
    from transacciones
    where deposito>=10000 and fecha=’01/01/2007′ and
    cuenta in (select cuenta from transacciones
    where deposito>=10000 and fecha=’01/01/2007′
    group by cuenta
    having count(*)>4)

    Ya lo eh intentado pero no me trae los mismo resultados, y la tabla no contiene valores nulos ojala puedas ayudarme, te felicito mucho y te mando saludos Atte. Marco

  10. Anónimo
    • 15 febrero, 2008
    • 2:47 pm
    • Reply

    Respecto a tu conclusión:
    «Generalmente, IN resulta más ventajoso cuando la ejecución de la subconsulta tiene poco coste respecto a la principal, y EXISTS cuando sucede al revés.»

    ¿Cabe esperar que los resultados varien dependiendo de la versión de oracle que se utilice?

    Un saludo

  11. Anónimo
    • 14 marzo, 2008
    • 8:29 pm
    • Reply

    Necesito migrar unos sp’s de SQL2000 a SQL2005 y tengo sentencias de forzado de indices como:

    select count(*)
    from cj_alarma (2),tm_cajero,tm_user_sector
    where al_visto is null

    Que al compilar en SQL2005 me dan error.
    Cuál es la sintaxis en SQL2005 para forzar un indice???
    Gracias

  12. Javier Morales
    • 16 marzo, 2008
    • 1:08 pm
    • Reply

    Hola,

    La select sobre cuentas:
    Select cliente,cuenta,deposito,fecha
    from transacciones
    where deposito>=10000
    and fecha=’01/01/2007′
    and cuenta in (select cuenta
    from transacciones
    where deposito>=10000 and fecha=’01/01/2007′
    group by cuenta
    having count(*)>4)

    se implementaría así con EXIXTS,

    Select cliente,cuenta,deposito,fecha
    from transacciones t1
    where deposito>=10000
    and fecha=’01/01/2007′
    and EXISTS (select ‘existe’
    from transacciones t2
    where deposito>=10000 and fecha=’01/01/2007′
    AND t1.cuenta=t2.cuenta
    group by cuenta
    having count(*)>4)

    pero, ya que se trata de un cálculo sobre el total respecto a cada una de las filas, te recomiendo usar funciones analíticas para evitar que Oracle seleccione por cada fila de «cuentas» un select * from cuentas.

    El código SQL sería así:

    Select cliente, cuenta, deposito, fecha
    from (select cliente, cuenta, deposito, fecha,
    count(*) over (partition by cuenta) transporcuenta
    from transacciones
    where deposito>=10000
    and fecha=’01/01/2007′)
    where transporcuenta>4;

    Para el siguiente anónimo que menciona la frase «¿Cabe esperar que los resultados varien dependiendo de la versión de oracle que se utilice?»

    Pues sí, Oracle en cada versión puede tener un funcionamiento distinto del optimizador de costes (en principio, ir a mejor, claro). Coméntame tu caso y vemos cómo mejorarlo!

    ¿SQL2000 y SQL2005 son SQLServer? En ese caso no tengo ni idea. En Oracle, por ejemplo, una consulta «WHERE campo IS NULL» nunca utiliza un índice, ya que los valores nulos no se almacenan en los índices Oracle (salvo en unos índices muy concretos llamados índices bitmap, que se usan principalmente en entornos datawarehouse).

    No tengo ninguna experiencia con SQLServer. Lo siento.

    Espero haberos ayudado!
    Un saludo!
    Javier

  13. Ozkar
    • 26 marzo, 2008
    • 9:59 pm
    • Reply

    HOla Javier, es muy bueno tu blog, te queria pregunar como realizo una consulta a una tabla q tiene mas de 3 llaves primarias.

    Gracias por tu pronta ayuda.

    Slds

    Kelvin

  14. Javier Morales
    • 27 marzo, 2008
    • 11:21 am
    • Reply

    Hola Kelvin,

    No entiendo tu pregunta: una tabla sólo puede tener una clave primaria. Anda, échale un vistazo a este post sobre constraints.

    Gracias, un saludo,
    Javier

  15. Jaime
    • 15 julio, 2009
    • 5:05 pm
    • Reply

    Hola Javier, felicitaciones me sirvio mucho tu explicacion sobre IN y Exist, esta muy claro.Serias tan amable de publicar sobre,funciones,procedimiento,paquetes y triggers.

  16. Scooby
    • 10 septiembre, 2009
    • 8:32 pm
    • Reply

    Ozkar dijo…

    HOla Javier, es muy bueno tu blog, te queria pregunar como realizo una consulta a una tabla q tiene mas de 3 llaves primarias.

    Gracias por tu pronta ayuda.

    Slds

    Kelvin

    Aunque es un poco tarde para contestarle a kelvin, lo digo por la fecha "3/26/2008 10:59:00 PM" seguramente a lo que se refiere es que tiene una llave primaria compuesta de 3 campos o que 3 atributos son los que conforman la primary key.

  17. Javier Morales
    • 11 septiembre, 2009
    • 10:10 am
    • Reply

    Hola Ozkar, Scooby,

    select col1, col2, col3
    from tabla
    where campo1=valor1
    and campo2=valor2
    and campo3=valor3;

    😛 la primary key es sólo una condición que cumplen los valores de una o más columnas. En este caso, que no hay elementos duplicados ni valores nulos.

  18. Anónimo
    • 1 diciembre, 2009
    • 10:02 pm
    • Reply

    Wee! Javier esta excelente la pagina, te pido por favor desde tu optica me hables de los explain plans, ya que usas un lenguaje perfecto para entender, Saludos Gige desde Vzla!

  19. Javier Morales
    • 3 diciembre, 2009
    • 7:58 am
    • Reply

    Hola Gige,

    Los planes de ejecución son la herramienta clave para ver qué estrategia utilizará Oracle para resolver una consulta.

    Es vital para optimizar SQL.

    Visita http://optimizacionsql.blogspot.com donde verás ejemplos de cómo usarlo para "diagnosticar" qué diablos le está pasando a una query que, inexplicablemente, tarda minutos y horas estando formalmente bien escrita!

    Un saludo, y gracias!
    Javier

  20. ale vazmo
    • 14 julio, 2010
    • 8:57 am
    • Reply

    Hola, como puedo hacer un drop constraint chequeando si xiste el constraint

  21. Farid Gámez
    • 17 marzo, 2011
    • 10:02 pm
    • Reply

    Excelente, bien explicado

  22. Anónimo
    • 25 mayo, 2014
    • 8:07 pm
    • Reply

    Hola, como puedo hacer un procedimiento que sustituya al natural join, que riciba 2 tablas y las una. Con pl/sql… De antemano, gracias

  23. Javier Morales
    • 26 mayo, 2014
    • 10:28 pm
    • Reply

    Hola Anónimo:

    No deberías implementar ese procedimiento, porque no te aporta nada (más que lentitud e ineficiencia).

    Ese procedimiento debería retornar un REF CURSOR, y tendrías que recorrerlo igualmente… definir el cursor y darle el valor de una query dinámica (con las dos tablas que pasases como entrada al procedimiento) y nada te evita hacer el natural join entre ellas. Luego tendrías que recorrerlo…

    ¿Por qué quieres hacer manualmente con PL/SQL lo que Oracle hace de forma natural con SQL?

    No lo hagas, no ganarás nada y perderás (tiempo y eficiencia).

    Un saludo,
    Javier

  24. Anónimo
    • 9 febrero, 2016
    • 1:24 am
    • Reply

    Hola buen dia,

    espero me puedan apoyar con mi caso es que tengo una consulta en el cual utilizo el exists
    pero son alrededor de 190,000 registros y se tarda 2 horas en hacer un update me podria apoyar a ver de que otra manera puedo implementar el update:
    UPDATE ra_interface_distributions_all rida
    SET rida.interface_line_attribute6 = NULL,
    rida.interface_line_attribute8 = 'VALIDADO'
    WHERE rida.interface_line_attribute6 = ps_batch_id
    AND EXISTS
    (SELECT 'X'
    FROM ra_interface_lines_all rila
    WHERE rila.interface_line_attribute6 = ps_batch_id
    AND NVL (rida.interface_line_context, 'XX') =
    NVL (rila.interface_line_context, 'XX')
    AND NVL (rida.interface_line_attribute1, 'XX') =
    NVL (rila.interface_line_attribute1, 'XX')
    AND NVL (rida.interface_line_attribute2, 'XX') =
    NVL (rila.interface_line_attribute2, 'XX')
    AND NVL (rida.interface_line_attribute3, 'XX') =
    NVL (rila.interface_line_attribute3, 'XX')
    AND NVL (rida.interface_line_attribute4, 'XX') =
    NVL (rila.interface_line_attribute4, 'XX')
    AND NVL (rida.interface_line_attribute5, 'XX') =
    NVL (rila.interface_line_attribute5, 'XX')
    AND NVL (rida.interface_line_attribute6, 'XX') =
    NVL (rila.interface_line_attribute6, 'XX')
    AND NVL (rida.interface_line_attribute7, 'XX') =
    NVL (rila.interface_line_attribute7, 'XX'));

Deja una respuesta

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.