Selecting specific columns in MySQL - Step-by-Step Execution

Concept Flow - Selecting specific columns

Start Query

↓

Parse SELECT clause

↓

Identify columns requested

↓

Access table data

↓

Extract only requested columns

↓

Return result set with selected columns

↓

End Query

The database reads the SELECT statement, finds which columns to get, fetches only those columns from the table, and returns them as the result.

Execution Sample

MySQL

SELECT name, age FROM users;

This query fetches only the 'name' and 'age' columns from the 'users' table.

Execution Table

Step	Action	Evaluation	Result
1	Start query execution	Query: SELECT name, age FROM users;	Ready to parse
2	Parse SELECT clause	Columns requested: name, age	Columns identified
3	Access table 'users'	Table has columns: id, name, age, email	Table accessed
4	Extract columns	Only 'name' and 'age' selected	Data subset prepared
5	Return result set	Rows with only 'name' and 'age'	[('Alice', 30), ('Bob', 25), ('Carol', 27)]
6	End query	All requested data returned	Query complete

💡 Query ends after returning only the selected columns from all rows.

Variable Tracker

Variable	Start	After Step 2	After Step 4	Final
requested_columns	None	['name', 'age']	['name', 'age']	['name', 'age']
table_columns	None	None	['id', 'name', 'age', 'email']	['id', 'name', 'age', 'email']
result_set	None	None	Partial rows with selected columns	[('Alice', 30), ('Bob', 25), ('Carol', 27)]

Key Moments - 2 Insights

Why does the result only show 'name' and 'age' columns, not all columns?

Because in Step 2 and 4 of the execution_table, the query specifically identifies and extracts only the 'name' and 'age' columns, ignoring others.

What happens if you select a column that does not exist in the table?

The query would fail during Step 2 or 3 when parsing or accessing the table, because the requested column is not found in the table_columns list.

Visual Quiz - 3 Questions

Look at the execution_table, what columns are identified in Step 2?

A ['name', 'age']

B ['email']

C ['id', 'name', 'age']

D All columns

💡 Hint

Check the 'Evaluation' column in Step 2 of the execution_table.

At which step does the query prepare the data subset with only selected columns?

A Step 3

B Step 5

C Step 4

D Step 6

💡 Hint

Look for the step where 'Extract columns' action happens in the execution_table.

If you add 'email' to the SELECT clause, how would the 'requested_columns' variable change after Step 2?

A ['name', 'age']

B ['name', 'age', 'email']

C ['email']

D No change

💡 Hint

Refer to variable_tracker 'requested_columns' after Step 2 and imagine adding 'email'.

Concept Snapshot

SELECT column1, column2 FROM table_name;
- Retrieves only specified columns from the table.
- Reduces data returned, improving efficiency.
- Columns must exist in the table.
- Result set includes all rows but only chosen columns.

Full Transcript

This visual execution trace shows how a SQL query selecting specific columns works. The database starts by reading the query, then parses the SELECT clause to find which columns are requested. It accesses the table and identifies all columns available. Then it extracts only the requested columns from each row. Finally, it returns the result set containing only those columns for all rows. Variables like requested_columns and result_set change as the query progresses. Key moments include understanding why only selected columns appear and what happens if a column does not exist. The quiz tests knowledge of steps where columns are identified and extracted, and how variables change if the query changes.

Practice

1. What does the SQL statement SELECT name, age FROM users; do?

easy

A. It updates the name and age columns in the users table.

B. It retrieves all columns from the users table.

C. It deletes the name and age columns from the users table.

D. It retrieves only the name and age columns from the users table.

Solution

1. Step 1: Understand the SELECT statement

   The SELECT keyword is used to choose specific columns from a table.

2. Step 2: Identify the columns listed

   The columns listed after SELECT are name and age, so only these will be retrieved.

3. Final Answer:

   It retrieves only the name and age columns from the users table. -> Option D

4. Quick Check:

   Select specific columns = name, age [OK]

Hint: SELECT columns by listing names separated by commas [OK]

Common Mistakes:

• Thinking SELECT * returns specific columns
• Confusing SELECT with DELETE or UPDATE
• Forgetting commas between column names

2. Which of the following is the correct syntax to select the columns id and email from a table named customers?

easy

A. SELECT id email FROM customers;

B. SELECT id, email FROM customers;

C. SELECT (id, email) FROM customers;

D. SELECT * FROM customers WHERE id, email;

Solution

1. Step 1: Recall correct SELECT syntax

   Columns must be separated by commas without parentheses.

2. Step 2: Check each option

   SELECT id, email FROM customers; uses commas correctly: SELECT id, email FROM customers;.

3. Final Answer:

   SELECT id, email FROM customers; -> Option B

4. Quick Check:

   Columns separated by commas = SELECT id, email FROM customers; [OK]

Hint: Use commas between columns, no parentheses [OK]

Common Mistakes:

• Omitting commas between columns
• Using parentheses around columns
• Misusing WHERE clause for column selection

3. Given the table products with columns product_id, name, and price, what will be the output of this query?

SELECT name, price FROM products WHERE price > 100;

medium

A. Only the name and price of products with price greater than 100.

B. All columns for products with price greater than 100.

C. Only the product_id and price of products with price greater than 100.

D. Syntax error due to missing commas.

Solution

1. Step 1: Analyze the SELECT clause

   The query selects only name and price columns.

2. Step 2: Analyze the WHERE clause

   It filters rows where price > 100.

3. Final Answer:

   Only the name and price of products with price greater than 100. -> Option A

4. Quick Check:

   Select specific columns with condition = Only the name and price of products with price greater than 100. [OK]

Hint: SELECT columns listed, WHERE filters rows [OK]

Common Mistakes:

• Assuming all columns are returned
• Confusing WHERE with column selection
• Thinking missing commas cause error here

4. Identify the error in this query:

SELECT id name FROM employees;

medium

A. SELECT keyword is misspelled.

B. Table name is incorrect.

C. Missing comma between column names.

D. No error, query is correct.

Solution

1. Step 1: Check column list syntax

   Columns must be separated by commas. Here, id and name are missing a comma.

2. Step 2: Verify other parts

   Table name employees and SELECT keyword are correct.

3. Final Answer:

   Missing comma between column names. -> Option C

4. Quick Check:

   Comma missing between columns = Missing comma between column names. [OK]

Hint: Separate columns with commas to avoid syntax errors [OK]

Common Mistakes:

• Forgetting commas between columns
• Assuming space works as separator
• Misreading error as table or keyword issue

5. You have a table orders with columns order_id, customer_id, order_date, and total. You want to get only the order_id and total for orders placed in 2023. Which query is correct?

hard

A. SELECT order_id, total FROM orders WHERE YEAR(order_date) = 2023;

B. SELECT order_id total FROM orders WHERE order_date = 2023;

C. SELECT order_id, total FROM orders WHERE order_date LIKE '2023%';

D. SELECT * FROM orders WHERE order_date = 2023;

Solution

1. Step 1: Select specific columns

   The query must select order_id and total only, so SELECT order_id, total is correct.

2. Step 2: Filter orders from 2023

   Using WHERE YEAR(order_date) = 2023 correctly filters orders placed in 2023.

3. Step 3: Check other options

   SELECT order_id total FROM orders WHERE order_date = 2023; misses comma and uses wrong date filter. SELECT order_id, total FROM orders WHERE order_date LIKE '2023%'; uses LIKE which may work but is less precise. SELECT * FROM orders WHERE order_date = 2023; selects all columns, not specific ones.

4. Final Answer:

   SELECT order_id, total FROM orders WHERE YEAR(order_date) = 2023; -> Option A

5. Quick Check:

   Select columns + correct year filter = SELECT order_id, total FROM orders WHERE YEAR(order_date) = 2023; [OK]

Hint: Use YEAR() to filter dates, commas to select columns [OK]

Common Mistakes:

• Omitting commas between columns
• Using wrong date filter syntax
• Selecting all columns instead of specific ones