How a SQL SELECT Query Actually Runs

Why it exists

SQL's SELECT statement was designed to be declarative. It lets you describe the data you want to retrieve from a database without having to specify the step-by-step procedure for finding it. The database engine's query optimizer is responsible for figuring out the most efficient execution plan to fulfill your request.

The mental model

The order you write a SELECT query is not the order it runs. Think of it as a factory assembly line for your data. The query first gathers all the raw materials (FROM and JOIN clauses), then filters out defective parts (WHERE clause), groups similar items (GROUP BY), inspects the groups (HAVING), and only at the very end does it apply the final paint and polish (SELECT list) before sorting (ORDER BY) and shipping a specific batch (LIMIT).

How it works

The general logical processing of a SELECT statement follows a strict sequence. First, any temporary tables in the WITH clause are computed. Second, the FROM clause is processed, building a working set of rows from all specified tables and joins. Third, the WHERE clause filters this working set. Fourth, if a GROUP BY is present, the remaining rows are grouped, and aggregate functions are calculated. Fifth, the HAVING clause filters out entire groups. Sixth, the expressions in the SELECT list are finally computed. After that, operators like UNION are applied, followed by ORDER BY to sort the results, and finally LIMIT or OFFSET to select a specific window of rows.

When to use it

Use the SELECT statement for any and all data retrieval tasks in a relational database. This ranges from simple lookups like SELECT * FROM users WHERE id = 123; to complex analytical queries that join multiple tables, aggregate data, and compute new values. It is the fundamental command for reading data.

When not to use it

SELECT is for reading data, not changing it. To add new data, use INSERT. To modify existing data, use UPDATE. To remove data, use DELETE. While these commands can sometimes be used within a WITH clause, the primary purpose of a top-level SELECT statement is always data retrieval.

One canonical example

Consider this query: SELECT country, COUNT(id) AS purchase_count FROM users JOIN purchases ON users.id = purchases.user_id WHERE signup_date > '2023-01-01' GROUP BY country HAVING COUNT(id) > 10 ORDER BY purchase_count DESC LIMIT 5;. Logically, the database first joins users and purchases, then filters for recent users, then groups by country, then filters for active countries, then calculates the final columns, then sorts the results, and finally returns only the top 5. The alias purchase_count could not be used in the WHERE clause because it is defined in the SELECT list, which runs much later.