SQL Fundamentals

Understand the logical order of SQL execution to write efficient queries and debug unexpected results.

What it is

SQL (Structured Query Language) is a declarative language used to manage and retrieve data from relational databases. Unlike procedural languages where you tell the computer how to do something step-by-step, SQL tells the database what result you want. The database engine then determines the most efficient way to fetch that data.

A key mental model for beginners is distinguishing between how we write a query and how the database executes it. We write in a human-readable order (SELECT first), but the engine processes clauses in a specific logical sequence to filter and shape the data correctly.

Why it matters

• Debugging: Knowing the execution order explains why you cannot use column aliases defined in SELECT within the WHERE clause.
• Performance: Understanding when filtering happens helps you optimize queries by reducing dataset size early in the process.
• Correctness: It prevents common errors regarding aggregation and grouping logic.
• Interviews: Explaining execution order is a standard question in technical interviews for data roles.

Syntax or steps

The logical execution order of a standard SELECT statement is as follows:

1. FROM: Identifies the source table(s).
2. WHERE: Filters individual rows based on conditions.
3. GROUP BY: Aggregates rows into groups.
4. HAVING: Filters those groups.
5. SELECT: Chooses columns and calculates expressions.
6. DISTINCT: Removes duplicate rows from the result set.
7. ORDER BY: Sorts the final result set.
8. LIMIT: Restricts the number of returned rows.

Example

SELECT 
    department, 
    COUNT(employee_id) AS total_employees
FROM 
    employees
WHERE 
    hire_date > '2023-01-01'
GROUP BY 
    department
HAVING 
    COUNT(employee_id) > 5
ORDER BY 
    total_employees DESC
LIMIT 10;

Part-by-part explanation:

• FROM employees: The engine starts here, loading the raw data.
• WHERE hire_date...: Rows are filtered immediately. Only employees hired after Jan 1, 2023, remain.
• GROUP BY department: Remaining rows are bucketed by their department name.
• HAVING COUNT...: Departments with 5 or fewer employees are discarded.
• SELECT ...: Finally, the engine calculates the count and selects the department name for the output.
• ORDER BY ... LIMIT ...: The resulting list is sorted by employee count (highest first) and truncated to the top 10.

Common mistakes

• Using aliases in WHERE: You cannot reference total_employees in the WHERE clause because SELECT hasn't run yet. Use the original expression COUNT(employee_id) instead, or move the condition to HAVING.
• Filtering before joining: Placing filters in the WHERE clause after a join can sometimes be less efficient than filtering in subqueries or CTEs before the join, depending on the database optimizer.
• Ignoring NULLs: COUNT(column) ignores NULL values, while COUNT(*) counts all rows. Be explicit about which behavior you need.

When to use it

Use standard SQL for ad-hoc analysis and reporting. Compare it with NoSQL or Python Pandas below:

Practice

Guided Exercise: Write a query to find the average salary per job title from an employees table, excluding job titles with fewer than 3 people.

Challenge: Why does this query fail? SELECT avg_salary FROM (SELECT AVG(salary) as avg_salary FROM employees) WHERE avg_salary > 50000; vs SELECT AVG(salary) as avg_salary FROM employees WHERE avg_salary > 50000;

Hint: Look at the execution order. In the second query, avg_salary doesn't exist during the WHERE phase.

Quick check

Question: Can you use a column alias defined in the SELECT clause inside the WHERE clause?

Answer: No. The WHERE clause executes before SELECT, so the alias is not yet known to the engine.

Summary

SQL is powerful because it separates intent from implementation. Mastering the logical execution order (FROM → WHERE → GROUP → SELECT) is essential for writing correct queries and understanding why certain syntax rules exist. This knowledge forms the foundation for advanced analytics and performance tuning.