Querying from views in MySQL - Step-by-Step Execution

Concept Flow - Querying from views

Create View

↓

Store View Definition

↓

Run Query on View

↓

Database Expands View to Base Tables

↓

Execute Expanded Query

↓

Return Result Set

A view is a saved query. When you query a view, the database runs the saved query behind the scenes and returns the results.

Execution Sample

MySQL

CREATE VIEW active_customers AS
SELECT id, name FROM customers WHERE active = 1;

SELECT * FROM active_customers;

Create a view named active_customers showing only active customers, then select all rows from that view.

Execution Table

Step	Action	Query/Condition	Result/Output
1	Create view	CREATE VIEW active_customers AS SELECT id, name FROM customers WHERE active = 1;	View 'active_customers' saved with definition
2	Query view	SELECT * FROM active_customers;	Database expands to SELECT id, name FROM customers WHERE active = 1;
3	Execute expanded query	SELECT id, name FROM customers WHERE active = 1;	Returns rows where active=1
4	Return result	Result set from expanded query	[{"id":1, "name":"Alice"}, {"id":3, "name":"Charlie"}]
5	End	No more steps	Query complete

💡 Query ends after returning all rows matching the view's condition

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
View Definition	None	SELECT id, name FROM customers WHERE active = 1	Same	Same
Query	SELECT * FROM active_customers	Expanded to SELECT id, name FROM customers WHERE active = 1	Executed	Result set returned
Result Set	Empty	Empty	[{"id":1, "name":"Alice"}, {"id":3, "name":"Charlie"}]	Returned to user

Key Moments - 2 Insights

Why does querying a view run a different query behind the scenes?

Because a view stores a saved query definition. When you query the view, the database replaces the view name with its saved query and runs that expanded query (see execution_table step 2).

Does the view store data itself?

No, the view only stores the query definition, not data. Data is fetched fresh from base tables each time you query the view (see variable_tracker 'View Definition' vs 'Result Set').

Visual Quiz - 3 Questions

Look at the execution_table, what query does the database actually run when you query the view?

A SELECT * FROM active_customers

B CREATE VIEW active_customers AS SELECT id, name FROM customers WHERE active = 1

C SELECT id, name FROM customers WHERE active = 1

D SELECT id, name FROM customers

💡 Hint

Check execution_table step 2 where the view query is expanded

At which step does the database return the actual rows to the user?

A Step 4

B Step 1

C Step 3

D Step 5

💡 Hint

Look at execution_table step 4 where the result set is returned

If the base table 'customers' changes, what happens when you query the view again?

A The view shows old data stored inside it

B The view shows updated data from the base table

C The view query breaks

D The view caches data and does not update

💡 Hint

Remember views run the saved query fresh each time (see key_moments about data storage)

Concept Snapshot

CREATE VIEW view_name AS SELECT ...;
Query the view like a table: SELECT * FROM view_name;
The database runs the saved query behind the scenes.
Views do not store data, only query definitions.
Results reflect current data in base tables.

Full Transcript

A view is like a saved query. When you create a view, you tell the database to remember a query under a name. Later, when you query the view, the database replaces the view name with the saved query and runs it on the base tables. This means the view always shows current data from the base tables, not stored data. For example, creating a view active_customers that selects customers where active=1 lets you query active_customers as if it were a table. Behind the scenes, the database runs the original query and returns the matching rows. This process involves creating the view, storing its query, expanding the view query when used, executing it, and returning the results. Understanding this helps you use views to simplify complex queries and keep your database organized.