CREATE VIEW in SQL

👁ī¸ The CREATE VIEW statement is used to create a view, which is a virtual table based on the result of a SELECT query. Views simplify complex queries, improve security, and provide a reusable way to access data from one or more tables.

📖 What is CREATE VIEW?

The CREATE VIEW statement saves a SQL query as a named database object. Instead of repeatedly writing the same query, you can query the view just like a regular table.

Information

A standard SQL view usually does not store data itself. It retrieves data from the underlying table(s) whenever the view is queried.

đŸŽ¯ Why Use CREATE VIEW?

Views make SQL queries easier to write, maintain, and secure.

  • 📌 Simplify complex SQL queries.
  • 📌 Reuse frequently executed queries.
  • 📌 Hide sensitive columns.
  • 📌 Restrict access to selected data.
  • 📌 Improve application maintainability.

📋 Sample Table

StudentIDNameDepartmentEmail
101AliceComputer Sciencealice@example.com
102BobMathematicsbob@example.com
103CharliePhysicscharlie@example.com

📝 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, the view can be queried exactly like a table.

Select from View

SELECT *
FROM StudentInfo;

The database executes the stored query and returns the latest data from the underlying table.

🔗 Create a View with a WHERE Clause

Views can display only records that satisfy a specific condition.

Filtered View

CREATE VIEW ComputerScienceStudents AS
SELECT
    StudentID,
    Name,
    Department
FROM Students
WHERE Department = 'Computer Science';

🤝 Create a View Using JOIN

A view can combine data from multiple related tables.

View with INNER JOIN

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

This view provides student and course information without requiring users to write the join each time.

📊 Result of StudentCourses View

StudentIDNameCourseName
101AliceDatabase Systems
102BobData Structures

🔄 Replace or Modify a View

Some database systems allow an existing view definition to be replaced.

Replace a View

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

Important

Some database systems use CREATE OR REPLACE VIEW, while others provide an ALTER VIEW statement. Check the syntax supported by your database system.

🗑ī¸ Drop a View

Remove a view when it is no longer needed.

DROP VIEW

DROP VIEW StudentInfo;

Warning

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

⚖ī¸ CREATE VIEW vs CREATE TABLE

FeatureCREATE VIEWCREATE TABLE
Stores DataUsually No✅ Yes
Based OnA SELECT query.Column definitions.
Can Combine Multiple Tables✅ Yes❌ No
Main PurposeSimplify and secure data access.Store data.

đŸ’ŧ Real-World Example

An HR department should view employee names and departments but not salary information. A view exposes only the required columns while keeping sensitive data protected.

Employee Directory View

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

🗄ī¸ Database Compatibility

Database SystemCREATE VIEW Support
MySQL✅ Fully supported.
PostgreSQL✅ Fully supported.
SQL Server✅ Fully supported.
Oracle✅ Fully supported.
SQLite✅ Supported.

⚠ī¸ Common Mistakes

  • ❌ Assuming a view stores its own copy of the data.
  • ❌ Creating unnecessarily complex nested views.
  • ❌ Expecting every view to support INSERT, UPDATE, or DELETE.
  • ❌ Forgetting that changes to underlying tables affect view results.

Warning

Very complex views may reduce query performance because the database must execute the underlying query whenever the view is accessed.

⚠ī¸ Best Practices

Best Practice

Create views for frequently used queries, expose only the columns users need, use meaningful view names, avoid unnecessary nesting, document the purpose of each view, and review view performance as your database grows.

🚀 Key Points to Remember

  • 📌 CREATE VIEW creates a virtual table based on a SELECT query.
  • 📌 Views simplify complex SQL statements.
  • 📌 They improve security by hiding sensitive columns.
  • 📌 Views always reflect the current data in the underlying tables unless using database-specific materialized views.
  • 📌 Views can combine data from multiple tables using joins.
  • 📌 View support is available in all major relational database systems.
>>"A well-designed view provides a simple and secure window into complex database structures."

Summary

✅ The CREATE VIEW statement allows you to create reusable, virtual tables based on SQL queries. Views simplify data access, improve security, reduce query duplication, and make database applications easier to maintain by hiding unnecessary complexity.