Orden de procesamiento de consultas

Introducción a Oracle SQL

Hadrien Lacroix

Content Developer

¿Por qué importa el orden de procesamiento?

Optimiza tus consultas
- Sin resultados no deseados
- Ejecución más rápida

Ejemplo

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

Ejemplo

    SELECT BillingCountry, AVG(Total) > 100) AS Average
->  FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

Ejemplo

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
->  WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

Ejemplo

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
->  GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

Ejemplo

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
->  HAVING AVG(Total) > 100
    ORDER BY Average DESC

Ejemplo

->  SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

Ejemplo

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
->  ORDER BY Average DESC

¿Qué puede fallar?

SELECT BillingCountry, 
       AVG(Total) > 100) AS Average
FROM Invoice
WHERE BillingCity <> 'Paris' 
GROUP BY BillingCountry
HAVING AVG(Total) > 100
ORDER BY Average DESC

Los alias no se pueden usar en WHERE, GROUP BY ni HAVING
Los alias sí se pueden usar en ORDER BY

¿Qué puede fallar?

SELECT BillingCountry, 
       AVG(Total) > 100) AS Average
FROM Invoice
WHERE BillingCity <> 'Paris' 
GROUP BY BillingCountry
HAVING AVG(Total) > 100
ORDER BY Average DESC

Los valores agregados no se pueden filtrar en WHERE
Los valores agregados sí se pueden filtrar en HAVING

¿Qué puede fallar?

SELECT BillingCountry, 
       AVG(Total) > 100) AS Average
FROM Invoice
WHERE BillingCity <> 'Paris' 
GROUP BY BillingCountry
HAVING AVG(Total) > 100)
ORDER BY Average DESC

Las filas individuales no se pueden filtrar en HAVING
Las filas individuales sí se pueden filtrar en WHERE

Orden de ejecución de la consulta

FROM y JOIN: determinan qué datos se consultan
WHERE: filtra filas individuales
GROUP BY: agrupa filas
HAVING: filtra grupos
SELECT: selecciona columnas y aplica funciones a columnas
DISTINCT: elimina duplicados
UNION, UNION ALL, INTERSECT, MINUS: aplican operadores de conjuntos
ORDER BY: ordena filas

¡Vamos a practicar!

Introducción a Oracle SQL