How the database engine processes a SELECT in SQL - Visual Walkthrough

Concept Flow - How the database engine processes a SELECT

Receive SELECT query

↓

Parse query syntax

↓

Validate tables and columns

↓

Optimize query plan

↓

Execute query plan

↓

Fetch data rows

↓

Apply filters (WHERE)

↓

Apply sorting (ORDER BY)

↓

Return result set to user

The database engine takes a SELECT query, checks it, plans how to get data efficiently, runs the plan, filters and sorts data, then sends results back.

Execution Sample

SQL

SELECT name, age FROM users WHERE age > 20 ORDER BY age;

This query selects names and ages from the users table where age is over 20, sorted by age.

Execution Table

Step	Action	Details	Result
1	Receive query	SELECT name, age FROM users WHERE age > 20 ORDER BY age;	Query accepted
2	Parse query	Check syntax and structure	Syntax valid
3	Validate tables/columns	Check 'users' table and 'name', 'age' columns exist	Validation successful
4	Optimize query plan	Decide best way to access data (e.g., index scan)	Plan created
5	Execute query plan	Start reading data from 'users'	Rows fetched
6	Apply WHERE filter	Keep rows where age > 20	Filtered rows
7	Apply ORDER BY	Sort rows by age ascending	Sorted rows
8	Return results	Send final rows to user	Result set sent

💡 All steps completed, query result returned to user

Variable Tracker

Variable	Start	After Step 5	After Step 6	After Step 7	Final
rows	empty	all rows from users	rows with age > 20	rows sorted by age	final result set

Key Moments - 3 Insights

Why does the database parse the query before running it?

Parsing checks if the query is written correctly and understandable, preventing errors later. See execution_table step 2.

What happens during query optimization?

The engine plans the fastest way to get data, like choosing indexes. This is step 4 in the execution_table.

Why filter rows after fetching them?

Filtering applies conditions like WHERE to remove unwanted rows, shown in step 6 of the execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result after step 3?

A Syntax error found

B Rows fetched

C Validation successful

D Query plan created

💡 Hint

Check the 'Result' column in row for step 3 in execution_table

At which step does the database apply the WHERE filter?

A Step 5

B Step 6

C Step 7

D Step 8

💡 Hint

Look for 'Apply WHERE filter' in the 'Action' column in execution_table

If the query had no ORDER BY clause, which step would be skipped?

A Step 7

B Step 6

C Step 4

D Step 8

💡 Hint

Step 7 is 'Apply ORDER BY', see execution_table

Concept Snapshot

SELECT query processing steps:
1. Receive and parse query
2. Validate tables and columns
3. Optimize query plan
4. Execute plan and fetch rows
5. Apply WHERE filters
6. Apply ORDER BY sorting
7. Return results to user

Full Transcript

When you run a SELECT query, the database engine first receives and parses it to check for errors. Then it validates that the tables and columns exist. Next, it creates an efficient plan to get the data. The engine executes this plan by fetching rows from the table. It filters these rows based on the WHERE clause, then sorts them if ORDER BY is used. Finally, it sends the result set back to you.

Practice

1. Which step does the database engine perform first when processing a SELECT query?

easy

A. It sorts the results according to the ORDER BY clause.

B. It groups rows using the GROUP BY clause.

C. It reads the table and filters rows based on the WHERE clause.

D. It picks which columns to show based on the SELECT list.

Solution

1. Step 1: Understand the role of the WHERE clause

   The WHERE clause filters rows early to reduce data for later steps.

2. Step 2: Recognize the order of operations in SELECT

   The database first filters rows, then picks columns, groups, and sorts.

3. Final Answer:

   It reads the table and filters rows based on the WHERE clause. -> Option C

4. Quick Check:

   Filtering rows happens before selecting columns [OK]

Hint: Filtering rows happens before picking columns or sorting [OK]

Common Mistakes:

• Thinking ORDER BY happens before filtering
• Assuming columns are picked before filtering rows
• Confusing grouping with filtering order

2. Which of the following is the correct syntax to select only the name and age columns from a table called people?

easy

A. SELECT name age FROM people;

B. SELECT * FROM people WHERE name, age;

C. SELECT name AND age FROM people;

D. SELECT name, age FROM people;

Solution

1. Step 1: Recall correct SELECT syntax for columns

   Columns are listed separated by commas after SELECT.

2. Step 2: Check each option's syntax

   SELECT name, age FROM people; uses commas correctly; others use wrong keywords or miss commas.

3. Final Answer:

   SELECT name, age FROM people; -> Option D

4. Quick Check:

   Columns separated by commas in SELECT [OK]

Hint: Use commas to separate columns in SELECT [OK]

Common Mistakes:

• Using AND instead of commas between columns
• Trying to filter columns in WHERE clause
• Omitting commas between column names

3. Given the table employees with columns id, name, and salary, what will this query return?

SELECT name FROM employees WHERE salary > 50000 ORDER BY name;

medium

A. All employee names sorted alphabetically regardless of salary.

B. All employee names sorted alphabetically with salary above 50000.

C. All employee names with salary below 50000, unsorted.

D. Syntax error due to missing GROUP BY clause.

Solution

1. Step 1: Analyze the WHERE clause

   The query filters employees with salary greater than 50000.

2. Step 2: Understand ORDER BY effect

   Results are sorted by name alphabetically.

3. Final Answer:

   All employee names sorted alphabetically with salary above 50000. -> Option B

4. Quick Check:

   Filter then sort results [OK]

Hint: Filter rows first, then sort results [OK]

Common Mistakes:

• Ignoring the salary filter
• Thinking GROUP BY is needed for ORDER BY
• Assuming all rows are returned

4. What is wrong with this query?

SELECT id, name FROM users WHERE age > 18 ORDER BY;

medium

A. Missing column name after ORDER BY causes syntax error.

B. WHERE clause should come after ORDER BY.

C. SELECT must include all columns in ORDER BY.

D. ORDER BY cannot be used with WHERE.

Solution

1. Step 1: Check ORDER BY syntax

   ORDER BY must specify at least one column to sort by.

2. Step 2: Identify error in query

   The query ends ORDER BY; without column name, causing syntax error.

3. Final Answer:

   Missing column name after ORDER BY causes syntax error. -> Option A

4. Quick Check:

   ORDER BY needs a column name [OK]

Hint: Always specify column after ORDER BY [OK]

Common Mistakes:

• Placing WHERE after ORDER BY
• Thinking ORDER BY needs all SELECT columns
• Believing ORDER BY can't be used with WHERE

5. You want to find the average salary for each department but only for employees earning more than 40000. Which query correctly processes this?

hard

A. SELECT department, AVG(salary) FROM employees WHERE salary > 40000 GROUP BY department;

B. SELECT department, AVG(salary) FROM employees GROUP BY department WHERE salary > 40000;

C. SELECT department, AVG(salary) FROM employees GROUP BY salary > 40000;

D. SELECT department, AVG(salary) FROM employees HAVING salary > 40000 GROUP BY department;

Solution

1. Step 1: Apply WHERE before grouping

   Filter employees with salary > 40000 before grouping by department.

2. Step 2: Use GROUP BY on department and calculate average salary

   Group filtered rows by department and compute average salary.

3. Final Answer:

   SELECT department, AVG(salary) FROM employees WHERE salary > 40000 GROUP BY department; -> Option A

4. Quick Check:

   Filter rows before grouping for correct average [OK]

Hint: Use WHERE before GROUP BY to filter rows [OK]

Common Mistakes:

• Placing WHERE after GROUP BY
• Using HAVING without aggregation condition
• Grouping by a condition instead of column