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.

Practice

1. What is the main purpose of the SELECT statement in MySQL?

easy

A. To retrieve data from one or more tables

B. To delete data from a table

C. To update existing data in a table

D. To create a new table in the database

Solution

1. Step 1: Understand the role of SELECT

   The SELECT statement is designed to fetch or retrieve data stored in tables.

2. Step 2: Compare with other SQL commands

   DELETE removes data, UPDATE changes data, and CREATE TABLE makes new tables, so these are not for retrieving data.

3. Final Answer:

   To retrieve data from one or more tables -> Option A

4. Quick Check:

   SELECT = retrieve data [OK]

Hint: SELECT always fetches data, not changes or deletes [OK]

Common Mistakes:

• Confusing SELECT with DELETE or UPDATE
• Thinking SELECT creates tables
• Assuming SELECT modifies data

2. Which of the following is the correct syntax to select all columns from a table named employees?

easy

A. SELECT employees FROM *;

B. SELECT ALL employees;

C. SELECT * FROM employees;

D. GET * FROM employees;

Solution

1. Step 1: Recall correct SELECT syntax

   The correct syntax to select all columns is SELECT * FROM table_name;.

2. Step 2: Check each option

   SELECT * FROM employees; matches the correct syntax. The other options are invalid: table name before *, using ALL incorrectly, and using GET instead of SELECT.

3. Final Answer:

   SELECT * FROM employees; -> Option C

4. Quick Check:

   SELECT * FROM table = correct syntax [OK]

Hint: Use * to select all columns in SELECT [OK]

Common Mistakes:

• Using GET instead of SELECT
• Placing table name before *
• Using ALL keyword incorrectly

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

SELECT name FROM products WHERE price > 100;

medium

A. All product names with price less than 100

B. All product names with price greater than 100

C. All columns for products priced over 100

D. An error because price comparison is invalid

Solution

1. Step 1: Analyze the SELECT clause

   The query selects only the name column from the products table.

2. Step 2: Understand the WHERE condition

   The condition price > 100 filters rows where price is greater than 100.

3. Final Answer:

   All product names with price greater than 100 -> Option B

4. Quick Check:

   SELECT name WHERE price > 100 = product names over 100 [OK]

Hint: WHERE filters rows; SELECT chooses columns [OK]

Common Mistakes:

• Confusing greater than with less than
• Expecting all columns instead of just name
• Thinking the query causes an error

4. Identify the error in this query:

SELECT FROM customers WHERE age > 30;

medium

A. Missing column names after SELECT

B. Incorrect WHERE clause syntax

C. Table name should be after WHERE

D. No error, query is correct

Solution

1. Step 1: Check SELECT syntax

   SELECT must be followed by column names or * to specify what to retrieve.

2. Step 2: Review the query structure

   The query has SELECT but no columns listed before FROM, which is invalid syntax.

3. Final Answer:

   Missing column names after SELECT -> Option A

4. Quick Check:

   SELECT needs columns or * [OK]

Hint: Always specify columns or * after SELECT [OK]

Common Mistakes:

• Leaving SELECT empty
• Placing table name after WHERE
• Assuming WHERE syntax is wrong

5. You want to see only the email addresses of customers who live in 'New York' from the customers table. Which query correctly retrieves this data?

hard

A. SELECT email FROM customers WHERE city == 'New York';

B. SELECT * FROM customers WHERE city = New York;

C. SELECT email WHERE city = 'New York' FROM customers;

D. SELECT email FROM customers WHERE city = 'New York';

Solution

1. Step 1: Identify correct SELECT and WHERE syntax

   The correct syntax is SELECT column FROM table WHERE condition;. The city value must be in quotes.

2. Step 2: Evaluate each option

   SELECT email FROM customers WHERE city = 'New York'; uses correct syntax and quotes around 'New York'. SELECT * FROM customers WHERE city = New York; misses quotes, causing error. SELECT email WHERE city = 'New York' FROM customers; has wrong clause order. SELECT email FROM customers WHERE city == 'New York'; uses double equals which is invalid in SQL.

3. Final Answer:

   SELECT email FROM customers WHERE city = 'New York'; -> Option D

4. Quick Check:

   Correct syntax with quotes and order [OK]

Hint: Use single quotes for string values in WHERE [OK]

Common Mistakes:

• Omitting quotes around string values
• Using == instead of = in WHERE
• Placing WHERE before FROM