Why SELECT retrieves data in MySQL - Visual Breakdown

Concept Flow - Why SELECT retrieves data

Write SELECT query

↓

Send query to database

↓

Database parses query

↓

Database executes query

↓

Retrieve matching rows

↓

Return rows to user

This flow shows how a SELECT query is written, sent to the database, executed, and how matching data rows are retrieved and returned.

Execution Sample

MySQL

SELECT name, age FROM users WHERE age > 30;

This query retrieves the name and age of users older than 30 from the users table.

Execution Table

Step	Action	Evaluation	Result
1	Receive query	SELECT name, age FROM users WHERE age > 30;	Query accepted
2	Parse query	Check syntax and table existence	Syntax valid, table 'users' found
3	Execute query	Scan 'users' table rows	Rows scanned
4	Filter rows	age > 30 condition	Rows matching condition selected
5	Project columns	Select 'name' and 'age' columns	Selected columns prepared
6	Return result	Send rows to user	Result set sent

💡 All matching rows retrieved and sent to user, query execution complete

Variable Tracker

Variable	Start	After Step 3	After Step 4	After Step 5	Final
Query	NULL	SELECT name, age FROM users WHERE age > 30;	Same	Same	Same
Rows scanned	0	All rows in 'users' table	Same	Same	Same
Result set	Empty	Empty	Filtered rows	Projected columns only	Sent to user

Key Moments - 2 Insights

Why does the database scan all rows before filtering?

The database reads all rows to check which ones meet the WHERE condition, as shown in execution_table step 3 and 4.

Why do we select only certain columns after filtering rows?

After filtering rows, the database picks only requested columns (name, age) to send, as seen in step 5.

Visual Quiz - 3 Questions

Look at the execution_table, at which step does the database filter rows by age?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Check the 'Action' and 'Evaluation' columns in execution_table rows for filtering condition

According to variable_tracker, what is the state of 'Result set' after step 5?

A Empty

B Filtered rows with only selected columns

C Filtered rows with all columns

D All rows in 'users' table

💡 Hint

Look at the 'Result set' row under 'After Step 5' in variable_tracker

If the WHERE condition was removed, how would the execution_table change?

A Step 4 would select all rows without filtering

B Step 3 would not scan rows

C Step 5 would select different columns

D Step 6 would not return any rows

💡 Hint

Think about the filtering step and what happens if no condition is applied

Concept Snapshot

SELECT retrieves data by:
1. Receiving and parsing the query
2. Scanning table rows
3. Filtering rows by WHERE condition
4. Selecting requested columns
5. Returning the result set to the user

Full Transcript

When you write a SELECT query, the database first receives and parses it to check for errors and confirm the table exists. Then it scans all rows in the table to find those that match the WHERE condition. After filtering, it picks only the columns you asked for. Finally, it sends these rows back to you as the result. This process ensures you get exactly the data you requested.