SQL assembly line: the machine reads differently
About this app
Written SELECT … ORDER BY, executed FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY: the clauses jump into execution order and the tools table runs station by station along the line (rows, groups, group values, alias). Plus the typical mistakes alias in WHERE and group condition in WHERE.
With explain mode: an AI tutor walks you through the app, points out what matters in the scene and answers your questions.
Written versus executed
An SQL query is written in the order SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. The database evaluates it logically in a different order: FROM fetches the table, WHERE checks each row, GROUP BY forms groups, HAVING checks the groups, only then SELECT computes the result columns and assigns aliases, and ORDER BY sorts at the end. Each station works on the intermediate result of the previous one. This explains why an alias from SELECT does not exist in WHERE yet, but does in ORDER BY.
What the app shows
On the left are the six clauses of a query on the table tools. A switch makes them jump into execution order; SELECT moves from place 1 to place 5. On the right the intermediate result runs along the line: five rows after FROM, four after WHERE price > 400, three groups after GROUP BY tag, two after HAVING avg(price) > 450 and finally two result rows, B with 561.5 before A with 540.0. Filtered-out rows land visibly in a tray.
Try it yourself
Run the line station by station or tap a clause. Choose a second query with count(*) > 1 or the example from the lecture slide. Insert a typical mistake, such as the alias avg_price in WHERE: the line stops at WHERE, and the result panel shows the messages from PostgreSQL and SQLite.
Frequently asked questions
- In which order does SQL execute a query?
- Logically in the order FROM (with JOIN), WHERE, GROUP BY, HAVING, SELECT, ORDER BY and finally LIMIT. SELECT is written first. An optimizer may work differently internally, but the result must match this logical order.
- What is the difference between WHERE and HAVING?
- WHERE filters single rows before grouping. HAVING filters groups after GROUP BY has formed them. Conditions with aggregate functions such as count(*) > 1 or avg(price) > 450 therefore belong in HAVING.
- Why can't I use a SELECT alias in WHERE?
- WHERE is evaluated before SELECT, so the alias does not exist there yet. PostgreSQL reports column does not exist. ORDER BY runs after SELECT and may use the alias. SQLite resolves an alias without an aggregate function in WHERE as an extension.
- What can I try in the app?
- You switch between written and executed order, run the table through station by station, choose between three queries and insert typical mistakes that stop the line.
Subject: Computer science
More from Computer science
- Ackermann function
- Elevator simulator
- Binary workshop
- Binary versus linear search
- The Collatz problem
- Inside a CPU
- Swap the inside
- One frame in 16 milliseconds — how a game engine renders an image (3D)
A VisuApp by heyprof: interactive, free in your browser, no sign-up. What is a VisuApp? · All apps