Ordem de processamento da consulta

Introdução ao Oracle SQL

Hadrien Lacroix

Content Developer

Por que a ordem de processamento importa?

Otimize suas consultas
- Sem resultados indesejados
- Execução mais rápida

Exemplo

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

Exemplo

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

Exemplo

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

Exemplo

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

Exemplo

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

Exemplo

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

Exemplo

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

O que pode dar errado?

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

Apelidos não podem ser usados em WHERE, GROUP BY e HAVING
Apelidos podem ser usados em ORDER BY

O que pode dar errado?

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

Valores agregados não podem ser filtrados na cláusula WHERE
Valores agregados podem ser filtrados na cláusula HAVING

O que pode dar errado?

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

Linhas individuais não podem ser filtradas na cláusula HAVING
Linhas individuais podem ser filtradas na cláusula WHERE

Ordem de execução da consulta

FROM e JOINs: definem quais dados serão consultados
WHERE: filtra linhas individuais
GROUP BY: agrupa linhas
HAVING: filtra grupos
SELECT: seleciona colunas e aplica funções nas colunas
DISTINCT: remove duplicatas
UNION, UNION ALL, INTERSECT, MINUS: aplicam operadores de conjunto
ORDER BY: ordena linhas

Vamos praticar!

Introdução ao Oracle SQL