When we write an SQL query, we usually read it from top to bottom.

SELECT department, COUNT(*) FROM employees WHERE salary > 50000 GROUP BY department HAVING COUNT(*) > 5 ORDER BY department LIMIT 10;

But the database does not process this query exactly in the same order that we write it. Understanding the execution order helps us understand how databases process our queries and why some queries are faster than others.

===========SQL Execution Order===========
A simple way to remember the logical execution order is:

FROM WHERE GROUP BY HAVING SELECT DISTINCT ORDER BY LIMIT / OFFSET
  1. FROM First, the database finds the table that we want to use. 
SELECT * FROM employees;

The database starts with the employees table. If we use a JOIN, the database also needs to combine the required tables.

  1. WHERE Next, the database filters the rows. 
SELECT * FROM employees WHERE salary > 50000;

Only employees whose salary is greater than 50000 are kept.

 employees ↓ WHERE salary > 50000 ↓ matching rows

This step can remove a large number of rows before the next steps.

  1. GROUP BY Next, the database groups the remaining rows. 
SELECT department, COUNT(*) FROM employees WHERE salary > 50000 GROUP BY department;

Here, employees are grouped by department.

 IT → 20 employees HR → 8 employees Finance → 12 employees
  1. HAVING HAVING filters the groups created by GROUP BY. 
SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10;

Here, only departments with more than 10 employees are returned.

Difference between WHERE and HAVING.

 WHERE → filters rows HAVING → filters groups
  1. SELECT After filtering and grouping, the database produces the columns requested in the SELECT. 
SELECT department, COUNT(*) FROM employees GROUP BY department;
department count
IT 20
HR 8
Finance 12
  1. DISTINCT DISTINCT removes duplicate results. 
SELECT DISTINCT department FROM employees;

If the table contains:

 IT IT HR Finance Finance

Result:

 IT HR Finance
  1. ORDER BY ORDER BY sorts the final result. 
SELECT * FROM employees ORDER BY salary DESC;

The employees are sorted from the highest salary to the lowest salary.

  1. LIMIT / OFFSET Finally, LIMIT controls how many rows are returned. 
SELECT * FROM employees ORDER BY salary DESC LIMIT 10;

Only the first 10 rows are returned.

But

OFFSET can be used to skip rows.

LIMIT 10 OFFSET 20;

Result:

 Skip 20 rows ↓ Return the next 10 rows