Verwerkingsvolgorde van queries

Introductie tot Oracle SQL

Hadrien Lacroix

Content Developer

Waarom is de verwerkingsvolgorde belangrijk?

Optimaliseer je queries
- Geen ongewenste resultaten
- Snellere uitvoering

Voorbeeld

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

Voorbeeld

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

Voorbeeld

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

Voorbeeld

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

Voorbeeld

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

Voorbeeld

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

Voorbeeld

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

Wat kan er misgaan?

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

Aliassen kun je NIET gebruiken in WHERE, GROUP BY en HAVING
Aliassen kun je WEL gebruiken in ORDER BY

Wat kan er misgaan?

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

Geaggregeerde waarden kun je NIET filteren in WHERE
Geaggregeerde waarden kun je WEL filteren in HAVING

Wat kan er misgaan?

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

Individuele rijen kun je NIET filteren in HAVING
Individuele rijen kun je WEL filteren in WHERE

Uitvoervolgorde van een query

FROM en JOINs: bepaal welke data je opvraagt
WHERE: filter individuele rijen
GROUP BY: groepeer rijen
HAVING: filter groepen
SELECT: kies kolommen en pas functies toe
DISTINCT: verwijder dubbelen
UNION, UNION ALL, INTERSECT, MINUS: set-operatoren
ORDER BY: sorteer rijen

Laten we oefenen!

Introductie tot Oracle SQL