Views in SQL

👁ī¸ A View in SQL is a virtual table created from the result of a SELECT query. A view does not usually store the actual data itself; instead, it presents data from one or more underlying tables as if it were a regular table. Views simplify complex queries, improve security, and provide a consistent way to access data.

📖 What is a View?

A view is a saved SQL query that can be queried just like a table. Whenever you retrieve data from a view, the database executes the underlying query and returns the result.

Information

Most standard SQL views are virtual views. Some database systems also support materialized views, which store query results physically for improved performance.

đŸŽ¯ Why Use Views?

Views make database applications easier to build, maintain, and secure.

  • 📌 Simplify complex SQL queries.
  • 📌 Hide unnecessary columns.
  • 📌 Improve data security.
  • 📌 Provide a consistent interface for applications.
  • 📌 Reuse frequently executed queries.
  • 📌 Abstract changes in the underlying database schema.

📊 Sample Tables

Students

StudentIDNameDepartment
101AliceComputer Science
102BobMathematics

Enrollments

StudentIDCourse
101Database Systems
102Data Structures

📝 Basic Syntax

CREATE VIEW Syntax

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

💡 Create a Simple View

Create a view that displays only student names and departments.

Simple View

CREATE VIEW StudentInfo AS
SELECT Name, Department
FROM Students;

🔍 Query the View

Once created, a view can be queried just like a table.

Select from a View

SELECT *
FROM StudentInfo;

The database executes the underlying query and returns the requested data.

🔗 Create a View Using JOIN

Views can combine data from multiple tables, making complex joins easier to reuse.

View with INNER JOIN

CREATE VIEW StudentCourses AS
SELECT
    s.StudentID,
    s.Name,
    e.Course
FROM Students s
INNER JOIN Enrollments e
ON s.StudentID = e.StudentID;

Applications can now retrieve student and course information without writing the join repeatedly.

🔄 Update a View

Many database systems support modifying an existing view definition.

Replace or Alter a View

CREATE OR REPLACE VIEW StudentInfo AS
SELECT
    Name,
    Department
FROM Students
WHERE Department = 'Computer Science';

Important

Some databases use CREATE OR REPLACE VIEW, while others use ALTER VIEW. The supported syntax varies by database system.

🗑ī¸ Drop a View

Remove a view when it is no longer needed.

Drop a View

DROP VIEW StudentInfo;

Warning

Dropping a view removes only the view definition. The underlying tables and their data remain unchanged.

📊 Virtual View vs Materialized View

FeatureVirtual ViewMaterialized View
Stores Data❌ Usually No✅ Yes
Uses Latest Table Data✅ AlwaysAfter Refresh
Query SpeedDepends on the underlying query.Usually Faster for complex queries.
Storage RequirementMinimalAdditional Storage Required

⚖ī¸ View vs Table

FeatureViewTable
Stores DataUsually No✅ Yes
Created FromSQL QueryDatabase Structure
Can Join Multiple Tables✅ Yes❌ No
PurposeSimplify and secure data access.Store actual records.

đŸ’ŧ Real-World Example

A company's HR department needs employee names and departments but should not see salary information. A view can expose only the required columns while hiding sensitive data stored in the underlying table.

Employee View

CREATE VIEW EmployeeDirectory AS
SELECT
    EmployeeID,
    FullName,
    Department
FROM Employees;

⚠ī¸ Updatable Views

Some views allow INSERT, UPDATE, and DELETE operations, while others do not. Whether a view is updatable depends on its definition and the database system.

  • ✅ Simple views based on a single table are often updatable.
  • ⚠ī¸ Views containing joins, aggregate functions, GROUP BY, DISTINCT, or set operations are often read-only.

🗄ī¸ Database Compatibility

Database SystemView Support
MySQLSupports views and many updatable views.
PostgreSQLSupports views and materialized views.
SQL ServerSupports views and indexed views under specific conditions.
OracleSupports views and materialized views.
SQLiteSupports views with limited update capabilities.

⚠ī¸ Common Mistakes

  • ❌ Assuming every view is automatically updatable.
  • ❌ Creating unnecessarily complex nested views.
  • ❌ Expecting a standard view to store data permanently.
  • ❌ Forgetting that changes to underlying tables affect view results.

Warning

Complex views can impact query performance because the database must execute the underlying query each time the view is accessed unless a materialized or indexed view is used where supported.

⚠ī¸ Best Practices

Best Practice

Use views to simplify frequently used queries, expose only the required columns, assign meaningful names, avoid deeply nested views, document their purpose, and consider materialized or indexed views for expensive queries when supported by your database system.

🚀 Key Points to Remember

  • 📌 A view is a virtual table created from a SELECT query.
  • 📌 Views simplify complex queries and improve code reuse.
  • 📌 They help protect sensitive data by exposing only selected columns.
  • 📌 Standard views usually do not store data physically.
  • 📌 Updatability depends on the view definition and the database system.
  • 📌 Views are widely used to improve maintainability, readability, and security.
>>"Views provide a clean window into your data, hiding complexity while exposing exactly what users need."

Summary

✅ SQL views are virtual tables built from SQL queries that simplify data access, improve security, and encourage query reuse. By presenting only the necessary data while hiding implementation details, views make databases easier to maintain and applications easier to develop.