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.