Views — Reusable Query Logic
title: “Views (Virtual Tables)” part: 3 topic_number: 3 slug: “views” difficulty: “Advanced” prerequisites: “multiple-joins, aggregate-functions” —
Views — Reusable Query Logic
What Is It?
A view is a saved SQL query that acts like a virtual table. It doesn’t store data itself — it’s a “window” into your tables that runs the underlying query each time you access it.
Real-world analogy: Like a favorite filter/search you save in an app — instead of typing the same complex search every time, you click a saved shortcut.
When you’d use it:
- Simplify complex queries for repeated use
- Create abstractions for security (hide sensitive columns)
- Provide consistent interfaces for reports
- Encapsulate business logic
Syntax Breakdown
Create a View
1
2
3
4
CREATE VIEW view_name AS
SELECT ...
FROM ...
WHERE ...;
Use a View
1
SELECT * FROM view_name; -- Just like a table!
Update/Replace a View
1
2
CREATE OR REPLACE VIEW view_name AS
SELECT ... FROM ...;
Drop a View
1
2
DROP VIEW view_name;
DROP VIEW IF EXISTS view_name;
Basic Examples
Employee Summary View
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE VIEW employee_summary AS
SELECT
e.employee_id,
e.first_name,
e.last_name,
e.salary,
d.department_name,
d.location,
m.first_name AS manager_first_name,
m.last_name AS manager_last_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
LEFT JOIN employees m ON e.manager_id = m.employee_id;
Now use it:
1
2
3
SELECT * FROM employee_summary
WHERE location = 'New York'
ORDER BY last_name;
Expected output:
| employee_id | first_name | last_name | salary | department_name | location | manager_first_name | manager_last_name |
|---|---|---|---|---|---|---|---|
| 7 | George | Jones | 71000.00 | Legal | New York | Alice | Johnson |
| 2 | Frank | Miller | 93000.00 | Marketing | New York | Bob | Smith |
| 3 | Carol | Williams | 78000.00 | Sales | New York | Bob | Smith |
Simplified access to complex joined data.
Department Stats View
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE VIEW department_stats AS
SELECT
d.department_id,
d.department_name,
d.location,
COUNT(e.employee_id) AS employee_count,
ROUND(AVG(e.salary), 2) AS avg_salary,
MIN(e.salary) AS min_salary,
MAX(e.salary) AS max_salary,
SUM(e.salary) AS total_payroll
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name, d.location;
Use it:
1
2
3
SELECT * FROM department_stats
WHERE employee_count >= 2
ORDER BY avg_salary DESC;
Expected output:
| department_id | department_name | location | employee_count | avg_salary | min_salary | max_salary | total_payroll |
|---|---|---|---|---|---|---|---|
| 2 | Marketing | New York | 2 | 87500.00 | 82000.00 | 93000.00 | 175000.00 |
| 1 | Engineering | San Francisco | 3 | 85666.67 | 75000.00 | 95000.00 | 257000.00 |
| 4 | Finance | Chicago | 2 | 79000.00 | 66000.00 | 92000.00 | 158000.00 |
Pre-aggregated department analytics.
Going Deeper
Views on Views
1
2
3
4
5
6
7
-- Base view
CREATE VIEW active_employees AS
SELECT * FROM employees WHERE is_active = TRUE;
-- View built on another view
CREATE VIEW active_high_earners AS
SELECT * FROM active_employees WHERE salary >= 80000;
Use it:
1
2
3
SELECT first_name, last_name, salary
FROM active_high_earners
ORDER BY salary DESC;
Layered abstractions for cleaner queries.
Parameterized Queries (via Stored Procedures)
Views can’t have parameters directly, but you can work around it:
1
2
3
4
5
6
7
8
9
10
11
-- Create a view with all data
CREATE VIEW employee_detail AS
SELECT
e.*,
d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
-- Filter when using
SELECT * FROM employee_detail
WHERE department_name = 'Engineering'; -- "Parameter" via WHERE
Security: Hiding Sensitive Data
1
2
3
4
5
6
7
8
9
CREATE VIEW public_employee_info AS
SELECT
employee_id,
first_name,
last_name,
department_id,
hire_date
-- Deliberately exclude: salary, email, phone, etc.
FROM employees;
Grant access to view, not table:
1
2
GRANT SELECT ON public_employee_info TO 'reporting_user'@'localhost';
-- reporting_user can't see salaries!
Materialized View Alternative (via Table + Event)
MySQL doesn’t have native materialized views, but you can create a “snapshot” table:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Create result table
CREATE TABLE dept_stats_snapshot AS
SELECT
d.department_id,
d.department_name,
COUNT(e.employee_id) AS employee_count,
AVG(e.salary) AS avg_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;
-- Refresh it periodically (manually or via event)
TRUNCATE dept_stats_snapshot;
INSERT INTO dept_stats_snapshot
SELECT ...;
Why? Regular views recalculate every time. Snapshot tables are faster for expensive queries.
Pause and Predict: Can you INSERT/UPDATE/DELETE through a view?
Answer
**Sometimes yes, with restrictions!** **Updatable views** must: - Select from a single table (no JOINs) - Not use DISTINCT, GROUP BY, HAVING, UNION - Not use aggregate functions - Not use subqueries in SELECT **Example updatable view:** ```sql CREATE VIEW engineering_employees AS SELECT * FROM employees WHERE department_id = 1; -- This works: UPDATE engineering_employees SET salary = salary * 1.05 WHERE employee_id = 1; ``` **Complex views (with JOINs, aggregates) are read-only.**Watch Out — Common Mistakes
Mistake #1: Overusing Views for Performance
1
2
-- THINKING: "Views cache results for speed"
-- ❗ REALITY: Views re-run the query EVERY time
Views don’t cache data! They’re just stored queries.
For performance: Use snapshot tables or indexing instead.
Mistake #2: Creating Views with SELECT *
1
2
3
4
5
-- FRAGILE
CREATE VIEW employee_view AS
SELECT * FROM employees;
-- If you add/remove columns from employees, view breaks or behaves unexpectedly
Better:
1
2
3
-- • EXPLICIT columns
CREATE VIEW employee_view AS
SELECT employee_id, first_name, last_name, salary FROM employees;
Mistake #3: Circular Dependencies
1
2
3
-- IMPOSSIBLE
CREATE VIEW view_a AS SELECT * FROM view_b;
CREATE VIEW view_b AS SELECT * FROM view_a; -- Error: circular dependency!
Prevention: Plan view hierarchy carefully.
Edge Case Spotlight
View with WITH CHECK OPTION
1
2
3
4
5
6
7
8
9
10
11
CREATE VIEW high_earners AS
SELECT * FROM employees WHERE salary >= 80000
WITH CHECK OPTION;
-- This will FAIL:
INSERT INTO high_earners (first_name, last_name, salary)
VALUES ('Test', 'Person', 50000); -- Error: violates view's WHERE clause!
-- This works:
INSERT INTO high_earners (first_name, last_name, salary)
VALUES ('Test', 'Person', 85000);
WITH CHECK OPTION prevents inserts/updates that violate the view’s filter.
Renaming Columns in Views
1
2
3
4
5
CREATE VIEW employee_names (id, full_name) AS
SELECT employee_id, CONCAT(first_name, ' ', last_name)
FROM employees;
SELECT * FROM employee_names;
Result columns are now id and full_name (not employee_id and the CONCAT expression).
Try This
Exercise 1 (Guided)
Create a view called project_overview that shows project_name, status, team_size (count of employees), and total_hours. Use it to find all ‘In Progress’ projects with 3+ team members.
Hint
CREATE VIEW with JOIN and GROUP BY, then SELECT FROM view WHERE status AND team_size.Exercise 2 (Independent)
Create a view top_earners_by_dept that shows department_name, employee_name (first + last), and salary for the highest-paid employee in each department. Then query this view.
Hint
Use window functions (ROW_NUMBER) or correlated subquery to identify top earner per dept.Exercise 3 (Challenge)
Create a reporting dashboard view monthly_hiring_trend that shows year_month (‘2023-01’ format), hire_count, and cumulative_hires (running total). Then query it for the last 12 months.
Hint
DATE_FORMAT for year-month, GROUP BY, window function for cumulative, then filter when querying.Answer Key
Exercise 1 Answer
```sql -- Create view CREATE VIEW project_overview AS SELECT p.project_name, p.status, COUNT(ep.employee_id) AS team_size, SUM(ep.hours_allocated) AS total_hours FROM projects p LEFT JOIN employee_projects ep ON p.project_id = ep.project_id GROUP BY p.project_id, p.project_name, p.status; -- Use view SELECT * FROM project_overview WHERE status = 'In Progress' AND team_size >= 3 ORDER BY team_size DESC; ``` **Expected output:** | project_name | status | team_size | total_hours | |--------------|--------|-----------|-------------| | Website Redesign | In Progress | 4 | 500 | | Data Pipeline | In Progress | 3 | 390 | | Mobile App | In Progress | 3 | 370 | Active projects with substantial teams.Exercise 2 Answer
```sql -- Create view CREATE VIEW top_earners_by_dept AS SELECT d.department_name, CONCAT(e.first_name, ' ', e.last_name) AS employee_name, e.salary FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE NOT EXISTS ( SELECT 1 FROM employees e2 WHERE e2.department_id = e.department_id AND e2.salary > e.salary ) ORDER BY d.department_name; -- Use view SELECT * FROM top_earners_by_dept; ``` **Expected output:** | department_name | employee_name | salary | |-----------------|---------------|---------| | Engineering | Sam Clark | 95000.00 | | Finance | Henry Moore | 92000.00 | | Human Resources | Ivy Anderson | 74000.00 | | Legal | George Jones | 71000.00 | | Marketing | Frank Miller | 93000.00 | | Sales | Bob Smith | 82000.00 | | ... | ... | ... | Highest earner from each department.Exercise 3 Answer
```sql -- Create view CREATE VIEW monthly_hiring_trend AS SELECT DATE_FORMAT(hire_date, '%Y-%m') AS year_month, COUNT(*) AS hire_count, SUM(COUNT(*)) OVER (ORDER BY DATE_FORMAT(hire_date, '%Y-%m')) AS cumulative_hires FROM employees GROUP BY DATE_FORMAT(hire_date, '%Y-%m') ORDER BY year_month DESC; -- Use view (last 12 months) SELECT * FROM monthly_hiring_trend WHERE year_month >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 12 MONTH), '%Y-%m') ORDER BY year_month DESC; ``` **Expected output:** | year_month | hire_count | cumulative_hires | |------------|------------|------------------| | 2025-09 | 0 | 20 | | 2025-08 | 0 | 20 | | 2023-09 | 1 | 20 | | 2023-08 | 1 | 19 | | 2023-06 | 1 | 18 | | ... | ... | ... | Monthly hiring trends with running totals.Quick Recap
• Views are saved queries that act like tables
• Don’t store data — run query each time accessed
• Simplify complex queries for reuse
• Provide security via column hiding
• Can be updated if simple (single table, no aggregates)
• CREATE OR REPLACE to update existing views
• Avoid SELECT * for stability
• Not cached — use snapshot tables for performance
Up Next
Time for a Challenge! → Mini Challenge 10
You’ve learned recursive CTEs, date functions, and views! Test these advanced skills before moving to window functions.