Lesson 02 of 61

5 mins read

SQL Syntax, Clauses & Execution Order

In Plain English

SQL statements are composed of structured clauses (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY). Crucially, the order in which you WRITE a query differs completely from the logical order in which the database query engine EXECUTES it.

The Mental Model

"Writing SQL is like ordering a custom sandwich: you name the bread and toppings first (SELECT), but the chef in the kitchen starts by grabbing the ingredients from the fridge (FROM), washes them (WHERE), groups them (GROUP BY), and finally plates the sandwich (ORDER BY)."

Deep Dive: How It Works

Written Clause Order: `SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT`.

Logical Execution Order: `FROM & JOINs -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT/OFFSET`.

Why This Matters: You cannot use column aliases created in SELECT inside the WHERE clause because WHERE executes before SELECT evaluates.

Syntax Blueprint

Logical Execution Pipeline:
[1. FROM / JOIN] -> [2. WHERE] -> [3. GROUP BY] -> [4. HAVING] -> [5. SELECT] -> [6. ORDER BY] -> [7. LIMIT]

The database engine filters source tables (WHERE) before projecting requested columns (SELECT).

Core Rules to Remember

Execution Order: Understanding that FROM/WHERE runs before SELECT explains why column aliases fail in WHERE filters.

Semicolon Delimiters: Semicolons (;) delimit individual statements, mandatory when executing multiple batch commands.

Whitespace & Formatting: SQL is whitespace-insensitive: format queries across multiple indented lines for readability.

Common Beginner Traps & How to Fix Them

Mistake:

Trying to reference a calculated SELECT alias in the WHERE clause (`SELECT price * 1.1 AS total_price WHERE total_price > 100`).

Why it happens: WHERE executes before SELECT creates the alias.

How to fix: Repeat the expression in WHERE (`WHERE price * 1.1 > 100`) or use a subquery/CTE.

Pro Tip: In modern SQL, column aliases ARE accessible in ORDER BY because ORDER BY executes after SELECT.

Live Interactive Example

Hit Run Code to see it live

Query Structure and Order of Clauses

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: FROM loaded products, WHERE filtered to Electronics, SELECT projected name/price, ORDER BY sorted descending, and LIMIT took top 2.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Write a Filtered and Sorted Query

Query the `name` and `score` from `students` where `score >= 80` ordered by `score DESC`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.