SQL Views

A view is a saved SQL query that is stored in the database with a name. Once created, a view can be queried just like a regular table. However, a view does not store data itself — it always fetches data from the underlying tables dynamically when accessed.

Think of a view like a window in a building. The window does not hold the outside world — it simply provides a specific, framed view of it. Every time someone looks through the window, they see the current state of the outside.

Why Use Views?

• Simplicity — Complex queries with multiple JOINs or filters can be saved as a view. Once saved, the view is accessed with a simple SELECT instead of rewriting the complex query every time.
• Security — A view can expose only specific columns, hiding sensitive data like salaries or phone numbers from certain users.
• Reusability — A view can be used in multiple queries, reports, and applications without repeating the underlying logic.
• Maintainability — If the underlying logic changes, updating the view definition updates all queries that use it.

The Reference Table

Creating a View

Syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example 1: View of Science Students Only

CREATE VIEW ScienceStudents AS
SELECT StudentID, StudentName, City, Fees
FROM Students
WHERE Course = 'Science';

This creates a view called ScienceStudents. It contains only students enrolled in the Science course, and only the specified columns.

Querying a View

Once created, the view is queried like a normal table:

SELECT * FROM ScienceStudents;

Result:

The Fees column is shown but the Course column is hidden because it was not included in the view definition.

Using WHERE on a View

SELECT StudentName FROM ScienceStudents
WHERE City = 'Delhi';

Views can be further filtered with WHERE, just like tables.

Example 2: View With JOIN (Complex Query Simplified)

Suppose there are two tables — Students and Enrollments. Joining them repeatedly in every query is repetitive. A view can save this joined result:

CREATE VIEW StudentCourseDetails AS
SELECT s.StudentName, s.City, e.CourseName, e.Grade
FROM Students s
INNER JOIN Enrollments e ON s.StudentID = e.StudentID;

Now, accessing enrollment details is as simple as:

SELECT * FROM StudentCourseDetails;

Example 3: View for Security (Hiding Sensitive Data)

CREATE VIEW PublicStudentInfo AS
SELECT StudentID, StudentName, City, Course
FROM Students;

This view exposes only non-sensitive information (no Fees), which can be given to users who should not see financial data.

Updating a View Definition

To modify an existing view, use CREATE OR REPLACE VIEW:

CREATE OR REPLACE VIEW ScienceStudents AS
SELECT StudentID, StudentName, City, Course, Fees
FROM Students
WHERE Course = 'Science';

This replaces the old view definition with the new one. The view name stays the same.

Dropping a View

To permanently delete a view:

DROP VIEW ScienceStudents;

Dropping a view does not delete the underlying table data — it only removes the saved query.

Viewing All Views in a Database

SHOW FULL TABLES WHERE Table_type = 'VIEW';

Can Data Be Inserted or Updated Through a View?

In simple cases (views based on a single table with no GROUP BY, DISTINCT, or aggregate functions), data can sometimes be inserted or updated through a view. However, this is not recommended in practice and many views are intentionally read-only. Views are primarily used for reading data, not modifying it.

Limitations of Views

• A view does not store data — it re-runs the underlying query every time it is accessed.
• Views with complex queries (JOINs, GROUP BY, subqueries) are generally not updatable.
• Performance of a view depends on the complexity of its underlying query.

Key Points to Remember

• A view is a named, saved query — not a stored copy of data.
• Views simplify complex queries and make them reusable.
• Views can limit which columns are visible, providing a layer of security.
• Use CREATE OR REPLACE VIEW to update the view's definition.
• Dropping a view does not affect the underlying table or its data.

Summary

A view is a virtual table created from a stored SQL query. It looks and behaves like a table when queried but always reflects the latest data from the underlying tables. Views are powerful tools for simplifying complex queries, enforcing data security, and improving the maintainability of SQL code in real-world applications.