Ordinea de procesare a interogărilor

Introducere în Oracle SQL

Hadrien Lacroix

Content Developer

De ce contează ordinea de procesare?

Optimizați interogările
- Fără rezultate nedorite
- Execuție mai rapidă

Exemplu

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

Exemplu

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

Exemplu

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

Exemplu

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

Exemplu

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

Exemplu

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

Exemplu

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

Ce poate merge greșit?

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

Aliasurile nu pot fi utilizate în WHERE, GROUP BY și HAVING
Aliasurile pot fi utilizate în ORDER BY

Ce poate merge greșit?

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

Valorile agregate nu pot fi filtrate în clauza WHERE
Valorile agregate pot fi filtrate în clauza HAVING

Ce poate merge greșit?

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

Rândurile individuale nu pot fi filtrate în clauza HAVING
Rândurile individuale pot fi filtrate în clauza WHERE

Ordinea de execuție a interogării

FROM și JOIN: determină datele interogate
WHERE: filtrează rândurile individuale
GROUP BY: grupează rândurile
HAVING: filtrează grupurile
SELECT: selectează coloanele și aplică funcții
DISTINCT: elimină duplicatele
UNION, UNION ALL, INTERSECT, MINUS: aplică operatori pe mulțimi
ORDER BY: ordonează rândurile

Să exersăm!

Introducere în Oracle SQL