A View in MySQL is a virtual table based on the result of a SQL query. A view does not normally store a separate copy of the underlying table data. Instead, it stores the query definition and presents the query result like a table.
A View is a named SQL query that can be accessed like a table.
CREATE VIEW student_view AS
SELECT
name,
city
FROM students;After creating the view, you can query it using SELECT:
SELECT *
FROM student_view;Views can make database applications easier to manage.
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;Example:
CREATE VIEW active_students AS
SELECT
student_id,
name,
city
FROM students
WHERE status = 'Active';Suppose the students table contains many columns, but an application only needs student name and city.
CREATE VIEW student_basic_info AS
SELECT
name,
city
FROM students;Now you can query:
SELECT *
FROM student_basic_info;The view provides a simpler interface to the underlying query.
A View can contain filtering conditions.
CREATE VIEW active_students AS
SELECT
student_id,
name,
course_id
FROM students
WHERE status = 'Active';Query the view:
SELECT *
FROM active_students;Only rows satisfying the view's WHERE condition are returned.
After creating a View, you can use SELECT just like you would with a table.
SELECT
student_id,
name
FROM active_students;You can also filter the view result:
SELECT
student_id,
name
FROM active_students
WHERE city = 'Patna';Views can be based on JOIN queries.
CREATE VIEW student_course_view AS
SELECT
s.student_id,
s.name,
c.course_name,
c.fee
FROM students s
INNER JOIN courses c
ON s.course_id = c.id;Now:
SELECT *
FROM student_course_view;can display student and course information together.
A View can combine information from several related tables.
CREATE VIEW student_payment_view AS
SELECT
s.student_id,
s.name,
c.course_name,
p.amount,
p.payment_date
FROM students s
INNER JOIN courses c
ON s.course_id = c.id
INNER JOIN payments p
ON s.student_id = p.student_id;This creates a reusable student payment report.
A View can contain calculated expressions.
CREATE VIEW student_due_view AS
SELECT
s.student_id,
s.name,
c.course_name,
c.fee,
s.paid_fee,
c.fee - s.paid_fee AS due_amount
FROM students s
INNER JOIN courses c
ON s.course_id = c.id;The due amount is calculated when the view query is evaluated.
Views can be created from aggregate queries.
CREATE VIEW course_student_count AS
SELECT
c.course_name,
COUNT(s.student_id) AS total_students
FROM courses c
LEFT JOIN students s
ON c.id = s.course_id
GROUP BY c.id, c.course_name;Query the view:
SELECT *
FROM course_student_count;CREATE VIEW course_fee_report AS
SELECT
course_id,
COUNT(*) AS total_students,
AVG(fee) AS average_fee
FROM students
GROUP BY course_id;This View stores the definition of a grouped query.
HAVING can be included in the query used to create a View.
CREATE VIEW popular_courses AS
SELECT
c.course_name,
COUNT(s.student_id) AS total_students
FROM courses c
INNER JOIN students s
ON c.id = s.course_id
GROUP BY c.id, c.course_name
HAVING COUNT(s.student_id) > 5;The view contains courses having more than five matching students.
You can inspect the SQL definition of a View using:
SHOW CREATE VIEW student_course_view;This displays the SQL statement used to define the View.
You can use SHOW FULL TABLES to identify tables and views in a database.
SHOW FULL TABLES;You can also filter the result to views:
SHOW FULL TABLES
WHERE Table_type = 'VIEW';If a View already exists, you can replace its definition using:
CREATE OR REPLACE VIEW active_students AS
SELECT
student_id,
name,
city,
course_id
FROM students
WHERE status = 'Active';This changes the View definition without first dropping it.
MySQL also supports ALTER VIEW for changing a View definition.
ALTER VIEW active_students AS
SELECT
student_id,
name,
city
FROM students
WHERE status = 'Active';CREATE OR REPLACE VIEW is often convenient when you want to replace the existing definition.
To remove a View, use DROP VIEW.
DROP VIEW student_basic_info;The View is removed, but dropping a View does not drop the underlying base tables.
To avoid an error when the View may not exist, use:
DROP VIEW IF EXISTS student_basic_info;This is useful in scripts and deployment operations.
| Table | View |
|---|---|
| Stores table data | Stores a query definition |
| Has its own rows and columns | Presents the result of its underlying query |
| Can be directly populated with INSERT | May or may not be updatable depending on its definition |
| Usually represents stored data | Usually provides a virtual representation of data |
| View | Temporary Table |
|---|---|
| Stores a query definition | Stores temporary result data |
| Can be referenced by multiple sessions according to normal object privileges | Visible only within the session that created it |
| Normally remains until explicitly dropped | Automatically disappears when the session ends |
Some Views are updatable, meaning INSERT, UPDATE, or DELETE operations may be possible through them.
However, not every View is updatable. Views containing features such as certain joins, aggregate functions, GROUP BY, DISTINCT, or other constructs may not be directly updatable.
CREATE VIEW simple_students AS
SELECT
student_id,
name,
city
FROM students;A simple View like this may be updatable, depending on its definition and other MySQL rules.
If a View is updatable, changes made through the View can affect the underlying table.
UPDATE simple_students
SET city = 'Patna'
WHERE student_id = 1;The underlying students table can be updated through the View when the View is updatable.
A View can expose only selected columns instead of giving users direct access to every column in a base table.
CREATE VIEW public_student_info AS
SELECT
student_id,
name,
city
FROM students;For example, sensitive columns such as internal notes or other restricted information can be excluded from this View.
CREATE VIEW payment_report AS
SELECT
s.student_id,
s.name,
c.course_name,
p.amount,
p.payment_date
FROM students s
INNER JOIN courses c
ON s.course_id = c.id
INNER JOIN payments p
ON s.student_id = p.student_id;Now the application can simply use:
SELECT *
FROM payment_report
ORDER BY payment_date DESC;This avoids repeating the complete JOIN query every time.
A View can be created from date-based conditions.
CREATE VIEW recent_payments AS
SELECT
student_id,
amount,
payment_date
FROM payments
WHERE payment_date >= '2026-01-01';Query the View:
SELECT *
FROM recent_payments;A normal MySQL View is generally a stored query definition rather than a separately stored copy of its result.
When you query a View, MySQL processes the View definition together with the outer query according to its optimizer and view algorithm rules.
For this reason, a View does not automatically make a complex query faster.
A View definition can contain ORDER BY in supported situations, but the final ordering of a SELECT from a View should generally be specified in the outer query when a particular output order is required.
CREATE VIEW student_names AS
SELECT
student_id,
name
FROM students;Then:
SELECT *
FROM student_names
ORDER BY name ASC;This makes the desired output order explicit.
A View can provide a reusable data source for an application dashboard.
CREATE VIEW student_dashboard AS
SELECT
s.student_id,
s.name,
c.course_name,
c.fee,
s.paid_fee,
c.fee - s.paid_fee AS due_amount,
s.status
FROM students s
LEFT JOIN courses c
ON s.course_id = c.id;The application can then use:
SELECT *
FROM student_dashboard
WHERE status = 'Active'
ORDER BY name;This keeps the repeated JOIN and due calculation inside one reusable database object.
CREATE TABLE courses (
id INT AUTO_INCREMENT PRIMARY KEY,
course_name VARCHAR(100),
fee DECIMAL(10,2)
);
CREATE TABLE students (
student_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
course_id INT,
city VARCHAR(100),
paid_fee DECIMAL(10,2),
status VARCHAR(20)
);
CREATE TABLE payments (
payment_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
amount DECIMAL(10,2),
payment_date DATE
);
INSERT INTO courses
(course_name, fee)
VALUES
('Python', 15000),
('Java', 18000),
('PHP', 12000),
('SQL', 10000);
INSERT INTO students
(name, course_id, city, paid_fee, status)
VALUES
('Amit Kumar', 1, 'Patna', 10000, 'Active'),
('Priya Singh', 2, 'Gaya', 18000, 'Active'),
('Rahul Sharma', 1, 'Patna', 7000, 'Active'),
('Neha Kumari', 3, 'Patna', 5000, 'Inactive');
INSERT INTO payments
(student_id, amount, payment_date)
VALUES
(1, 5000, '2026-01-10'),
(1, 5000, '2026-02-10'),
(2, 18000, '2026-03-15'),
(3, 7000, '2026-04-20');
-- Create a basic student View
CREATE VIEW active_students AS
SELECT
student_id,
name,
city,
course_id
FROM students
WHERE status = 'Active';
-- Create a student-course View
CREATE VIEW student_course_view AS
SELECT
s.student_id,
s.name,
s.city,
c.course_name,
c.fee,
s.paid_fee,
c.fee - s.paid_fee AS due_amount
FROM students s
INNER JOIN courses c
ON s.course_id = c.id;
-- Create a payment report View
CREATE VIEW payment_report AS
SELECT
s.student_id,
s.name,
c.course_name,
p.amount,
p.payment_date
FROM students s
INNER JOIN courses c
ON s.course_id = c.id
INNER JOIN payments p
ON s.student_id = p.student_id;
-- Create a course-wise student count View
CREATE VIEW course_student_count AS
SELECT
c.course_name,
COUNT(s.student_id) AS total_students
FROM courses c
LEFT JOIN students s
ON c.id = s.course_id
GROUP BY c.id, c.course_name;
-- Use the Views
SELECT *
FROM active_students;
SELECT *
FROM student_course_view
ORDER BY name;
SELECT *
FROM payment_report
ORDER BY payment_date DESC;
SELECT *
FROM course_student_count
ORDER BY total_students DESC;
-- Show View definitions
SHOW CREATE VIEW active_students;
SHOW CREATE VIEW student_course_view;
-- List Views
SHOW FULL TABLES
WHERE Table_type = 'VIEW';
-- Replace a View
CREATE OR REPLACE VIEW active_students AS
SELECT
student_id,
name,
city,
course_id,
paid_fee
FROM students
WHERE status = 'Active';
-- Drop a View
DROP VIEW IF EXISTS payment_report;This complete example demonstrates creating Views, querying Views, JOIN-based Views, aggregate Views, replacing a View, inspecting View definitions, listing Views, and dropping a View.
Question: What is a MySQL View?