What is a SQL View? Benefits and Limitations Explained

Throughout this course, you've written the same complex JOIN queries repeatedly — combining appointments with patients, doctors, and departments. A SQL view lets you save a query's definition permanently under a name, so instead of retyping a complex JOIN every time, you can query the view exactly like a simple table. A view doesn't store data itself; it stores the query, re-running it fresh every time the view is used.

Key Definitions

• View: A saved, named SQL query that behaves like a virtual table — it has no data of its own, but instead runs its underlying query fresh every time it's referenced.
• Virtual table: A table-like object whose contents are computed on demand from an underlying query, rather than being physically stored.
• Base table: The actual, physical table(s) that a view's underlying query draws its data from.

What You'll Learn

• Define what a SQL view is and how it differs from a physical table.
• Understand the benefits views provide: reusability, simplicity, and security.
• Recognize the key limitations of views compared to physical tables.
• Preview how views will be created and used throughout this module.

Detailed Explanation

A view is essentially a stored, named SELECT statement. Once created, you can write `SELECT * FROM completed_appointments_view` instead of retyping the full JOIN and WHERE clause every single time you need that same combined, filtered data. Behind the scenes, MySQL re-executes the view's underlying query against the current data in the base tables every time the view is queried — a view never stores a stale snapshot; it always reflects live data.

The benefits are substantial. Reusability: a complex multi-table JOIN gets written once and reused everywhere, reducing duplicated logic and the risk of subtly different versions of 'the same' query drifting apart across an application. Simplicity: application code, or less SQL-experienced team members, can query a viewer-friendly view rather than needing to understand a complex underlying JOIN structure. Security: a view can expose only specific columns or rows from a sensitive base table, letting you grant access to the view without granting direct access to the full underlying table — for example, a view showing doctor_name and department_name but deliberately excluding salary.

The key limitations: because a view has no data of its own, querying it always incurs the cost of running its underlying query fresh, meaning a view built on a slow, complex JOIN doesn't automatically become faster just by being wrapped in a view. Views also have restrictions on when they can be used for INSERT and UPDATE operations, covered in Lesson 9.3, and MySQL's standard views are not the same as materialized views, which physically cache results, a distinction explored in Lesson 9.6.

Visual Summary

A diagram showing a view as a 'window': a box labeled [completed_appointments_view] sits between the user and three base table boxes [appointments], [patients], [doctors]. An arrow labeled 'Query the view' goes from the user into the view box, and the view box has arrows fanning out to all three base tables, labeled 'Re-executes underlying JOIN query live, every time'.

Quick Reference

Aspect	View	Physical Table
Stores data?	No — stores only the query definition	Yes — stores actual rows
Reflects live changes?	Always, since it re-runs the query each time	N/A — it IS the live data
Query performance	Same cost as running the underlying query directly	Depends on indexes and table size
Common use	Simplifying complex JOINs, restricting column/row access	Primary data storage

SQL Example

CREATE TABLE departments (
  department_id   INT PRIMARY KEY AUTO_INCREMENT,
  department_name VARCHAR(100) NOT NULL
);

CREATE TABLE doctors (
  doctor_id       INT PRIMARY KEY AUTO_INCREMENT,
  doctor_name     VARCHAR(100) NOT NULL,
  department_id   INT,
  salary          INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE patients (
  patient_id      INT PRIMARY KEY AUTO_INCREMENT,
  patient_name    VARCHAR(100) NOT NULL,
  city            VARCHAR(80)
);

CREATE TABLE appointments (
  appointment_id    INT PRIMARY KEY AUTO_INCREMENT,
  patient_id        INT,
  doctor_id         INT,
  appointment_date  DATE,
  status            VARCHAR(20),
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  FOREIGN KEY (doctor_id)  REFERENCES doctors(doctor_id)
);

CREATE TABLE bills (
  bill_id         INT PRIMARY KEY AUTO_INCREMENT,
  appointment_id  INT,
  amount          DECIMAL(10,2),
  paid            BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id)
);

INSERT INTO departments VALUES
  (1, 'Cardiology'), (2, 'Orthopedics'), (3, 'Neurology'), (4, 'Dermatology');

INSERT INTO doctors (doctor_id, doctor_name, department_id, salary) VALUES
  (101, 'Dr. Verma', 1, 95000), (102, 'Dr. Iyer', 2, 78000),
  (103, 'Dr. Sen', 3, 120000), (104, 'Dr. Khan', 1, 88000);

INSERT INTO patients VALUES
  (201, 'Amit Rao', 'Pune'), (202, 'Neha Joshi', 'Mumbai'),
  (203, 'Karan Mehta', 'Delhi'), (204, 'Divya Nair', 'Pune');

INSERT INTO appointments VALUES
  (301, 201, 101, '2026-05-01', 'Completed'),
  (302, 202, 102, '2026-05-02', 'Completed'),
  (303, 203, 101, '2026-05-03', 'Cancelled'),
  (304, 204, 103, '2026-05-04', 'Completed');

INSERT INTO bills (appointment_id, amount, paid) VALUES
  (301, 1500.00, TRUE), (302, 2200.00, FALSE),
  (303, 800.00, TRUE), (304, 3000.00, TRUE);

-- A view that simplifies a common multi-table JOIN
CREATE VIEW completed_appointments_view AS
SELECT
  a.appointment_id,
  p.patient_name,
  d.doctor_name,
  a.appointment_date
FROM appointments a
JOIN patients p ON a.patient_id = p.patient_id
JOIN doctors d ON a.doctor_id = d.doctor_id
WHERE a.status = 'Completed';

-- Querying the view is now as simple as querying a regular table
SELECT * FROM completed_appointments_view;

The CREATE VIEW statement saves this three-table JOIN permanently under the name completed_appointments_view. From now on, anyone querying `SELECT * FROM completed_appointments_view` gets the same result as manually writing the full JOIN, without needing to retype or even understand its underlying structure — and if a new completed appointment is added to the base tables tomorrow, the view reflects it immediately, since it re-runs the query live every time.

Real-World Examples

• Business intelligence tools often query pre-built views rather than raw tables, so analysts don't need to understand the underlying complex JOIN structure of the source database.
• Companies use views to expose a restricted, safe subset of sensitive tables (like hiding salary columns) to broader teams without granting direct table access.
• Application backends frequently define views for commonly-needed combined data, reducing duplicated JOIN logic scattered across many different application queries.

Common Mistakes to Avoid

• Assuming a view automatically improves query performance simply because it's a 'view' rather than a raw query.
• Confusing a standard SQL view with a materialized view, which does physically cache results (covered in Lesson 9.6).
• Overusing views for extremely simple single-table queries where the added abstraction provides little benefit.

Interview Questions

Q1. What is a SQL view?

A SQL view is a saved, named SELECT query that behaves like a virtual table. It doesn't store data itself; instead, it re-executes its underlying query against the base tables every time it's referenced, always reflecting current data.

Q2. What are the main benefits of using a view?

Views provide reusability by saving complex JOIN logic under a simple name, simplicity by letting others query a view without understanding its underlying complexity, and security by exposing only specific columns or rows from a sensitive base table.

Q3. Does querying a view improve performance compared to running the underlying query directly?

No. A standard MySQL view has no data of its own and re-executes its underlying query every time it's used, so it incurs the same performance cost as running that query directly — a view simplifies syntax, but doesn't inherently speed up execution.

Practice MCQs

1. What does a SQL view store?

1. A physical copy of the data
2. Only the query definition, not the data itself
3. An index of the base table
4. A backup of the base table

Answer: B. Only the query definition, not the data itself

Explanation: A view stores only its underlying SELECT query, re-executing it against the base tables every time it's queried, rather than storing a physical copy of data.

2. Does a standard MySQL view improve query performance compared to the raw underlying query?

1. Yes, always significantly faster
2. No, it runs the same underlying query with the same cost
3. Only for INSERT statements
4. Views cannot be queried at all

Answer: B. No, it runs the same underlying query with the same cost

Explanation: Since a view has no stored data of its own, it re-executes its underlying query fresh each time, incurring the same performance cost as running that query directly.

Quick Revision Points

• A view is a virtual table storing only a query definition, not physical data.
• Views always reflect live data, since they re-execute their underlying query on every reference.
• Views do not inherently improve performance — they simplify syntax and can restrict access.

Conclusion

• A view saves a SELECT query under a name, letting it be reused like a simple table.
• Views provide reusability, simplicity, and security benefits without storing data themselves.
• Views do not automatically improve performance, since they re-run their underlying query every time.

A SQL view is a saved, named query that functions as a virtual table, always reflecting live data by re-executing its underlying SELECT statement each time it's referenced. Views offer significant benefits — reusability of complex JOIN logic, simplified querying for less SQL-experienced users, and security through restricted column or row access — but they do not store data themselves and therefore don't inherently improve query performance, a key limitation distinguishing them from the materialized views concept covered later in this module.