The Order SQL Runs In

You write SELECT first, but the database runs it almost last. Knowing the real order explains most confusing errors.

The order SQL actually runs in:

1. FROM and JOIN: gather the rows
2. WHERE: filter single rows
3. GROUP BY: form groups
4. HAVING: filter groups
5. SELECT: compute columns and aliases
6. ORDER BY: sort
7. LIMIT: cut

That's why this fails. WHERE runs before any groups exist, so there's no count to check yet:

SELECT department_id, COUNT(*)
FROM employees
WHERE COUNT(*) > 3
GROUP BY department_id;

Move the condition to HAVING, which runs after grouping:

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 3;

It's also why ORDER BY can use an alias from SELECT while WHERE usually can't. SQLite is lenient here, but PostgreSQL, MySQL and SQL Server aren't.