I've been teaching SQL and noticed that many students struggle with understanding the execution order of queries. They often write them as if they're executed top to bottom, starting with SELECT, FROM, and WHERE, but that's not how databases operate, which leads to confusion and bugs. Common points of misunderstanding include:
- The inability to use column aliases in the WHERE clause, since WHERE executes before SELECT.
- Getting unexpected results from LEFT JOINs because the join occurs before the filtering, and they might not be aware of duplicate keys.
- Confusion around how HAVING filters differ from WHERE, especially regarding the GROUP BY clause.
I've found that visually demonstrating the execution order with small datasets helps clarify this process, and I even created a tool called qatabase.com for animating query execution. I'm curious about how others explain SQL execution order; do you have effective approaches, or do people mostly learn through trial and error?
1 Answer
In my experience, teaching SQL often starts with grasping relational algebra concepts before diving into SQL itself. I tell my students to think of the SQL execution process as moving from "bigger to smaller": you begin with the tables in the FROM clause, apply WHERE to filter down the rows, and then use SELECT to finalize the output columns. It gives them a clear mental model!
Also, demonstrating the execution order using the EXPLAIN or PROFILE commands in the database can greatly help clarify how everything runs under the hood.