Query processing order

Introduction to Oracle SQL

Hadrien Lacroix

Content Developer

Why does processing order matter?

  • Optimize your queries
    • No unwanted results
    • Faster execution
Introduction to Oracle SQL

Example

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

Example

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

Example

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

Example

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

Example

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

Example

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

Example

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

What could go wrong?

SELECT BillingCountry, 
       AVG(Total) > 100) AS Average
FROM Invoice
WHERE BillingCity <> 'Paris' 
GROUP BY BillingCountry
HAVING AVG(Total) > 100
ORDER BY Average DESC
  • Aliases can't be used in WHERE, GROUP BY, and HAVING
  • Aliases can be used in ORDER BY
Introduction to Oracle SQL

What could go wrong?

SELECT BillingCountry, 
       AVG(Total) > 100) AS Average
FROM Invoice
WHERE BillingCity <> 'Paris' 
GROUP BY BillingCountry
HAVING AVG(Total) > 100
ORDER BY Average DESC
  • Aggregated values can't be filtered out in the WHERE clause
  • Aggregated values can be filtered out in the HAVING clause
Introduction to Oracle SQL

What could go wrong?

SELECT BillingCountry, 
       AVG(Total) > 100) AS Average
FROM Invoice
WHERE BillingCity <> 'Paris' 
GROUP BY BillingCountry
HAVING AVG(Total) > 100)
ORDER BY Average DESC
  • Single rows can't be filtered out in the HAVING clause
  • Single rows can be filtered out in the WHERE clause
Introduction to Oracle SQL

Query order of execution

  1. FROM and JOINs: determine which data is being queried

  2. WHERE: filter individual rows

  3. GROUP BY: group rows

  4. HAVING: filter groups

  5. SELECT: select columns and apply functions on columns

  6. DISTINCT: remove duplicates

  7. UNION, UNION ALL, INTERSECT, MINUS: apply set operators

  8. ORDER BY: order rows

Introduction to Oracle SQL

Let's practice!

Introduction to Oracle SQL

Preparing Video For Download...