SQL Views | Creating & Managing Views

Views

Videos Discussion

As databases grow, queries often become:

Long and difficult to read

Repetitive across multiple reports

Hard to maintain and secure

In real-world applications, the same complex query may be used again and again by:

Developers

Analysts

Reporting tools

Writing that query every time is inefficient and error-prone. This is where SQL Views are used. A view allows us to save a query as a virtual table and reuse it easily.

What Is a View?

A view is:

A named SQL query

Stored in the database

Treated like a table when queried

Does not store data physically

In simple words:

A view is a window through which you see data, The data stays in the original tables

Whenever you query a view, the database runs the underlying query automatically.

Why Use Views?

Views are commonly used to:

Simplify complex queries

Improve code readability

Reuse SQL logic

Enhance data security

Provide consistent data access

They act as an abstraction layer between users and tables.

Creating a View

Views are created using the CREATE VIEW statement.

Syntax

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

Example: Creating a View for Active Employees

CREATE VIEW active_employees AS
SELECT emp_id, emp_name, department_id, salary
FROM employees
WHERE status = 'ACTIVE';

Explanation

The view stores the query logic

No data is copied or duplicated

The view behaves like a table

Querying a View

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

SELECT * FROM active_employees;

You can also apply filters

SELECT emp_name, salary
FROM active_employees
WHERE salary > 50000;

The database automatically applies the original view logic.

Views with Joins

Views are especially useful for hiding complex joins.

Example: Employee Department View

CREATE VIEW employee_details AS
SELECT e.emp_id, e.emp_name, d.department_name, e.salary
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

Now users can query

SELECT * FROM employee_details;

Without worrying about join logic.

Updating Data Through Views

Some views allow data modification.

You can use:

INSERT

UPDATE

DELETE

But only when the view is updatable.

When Is a View Updatable?

A view is usually updatable if:

It is based on a single table

It does not use:

1. GROUP BY

2. DISTINCT

3. Aggregate functions

4. Subqueries

Example: Updating Data Using a View

UPDATE active_employees
SET salary = salary + 5000
WHERE emp_id = 101;

This update affects the original employees table.

Creating Read-Only Views

To prevent modifications, use WITH CHECK OPTION.

CREATE VIEW high_salary_employees AS
SELECT emp_id, emp_name, salary
FROM employees
WHERE salary > 70000
WITH CHECK OPTION;

This ensures:

Only rows matching the condition can be inserted or updated

Data integrity is maintained

Replacing an Existing View

To modify a view definition, use:

CREATE OR REPLACE VIEW active_employees AS
SELECT emp_id, emp_name, department_id
FROM employees
WHERE status = 'ACTIVE';

This avoids dropping and recreating the view.

Dropping a View

If a view is no longer needed:

DROP VIEW active_employees;

This removes only the view, not the underlying tables.

Views Vs Tables

Feature	Table	View
Stores data	Yes	No
Physically exists	Yes	No
Reusable	Yes	Yes
Can restrict access	Limited	Yes

Security Benefits of Views

Views help:

Hide sensitive columns (like salary)

Restrict rows based on role

Grant access without exposing tables

Example:

HR sees salary

Others see employee names only

Two Minute Drill

Views are virtual tables
Created using CREATE VIEW
Do not store data physically
Simplify complex queries
Improve security and reuse
Some views are updatable
DROP VIEW removes only the view