Views in SQL

What Is a View?

A View is a virtual table created using a SQL SELECT statement.
It does not store data physically; instead, it stores the query definition and displays data dynamically from base tables.

Whenever a view is queried, the database executes the underlying SQL query.

Why Views Are Used

Simplify complex SQL queries
Improve security by restricting data access
Provide abstraction over database schema
Ensure consistency across applications
Reduce repetitive SQL code

Key Characteristics of Views

Acts like a table but is not a table
Stores query logic, not data
Always shows up-to-date data
Can be queried using SELECT
Some views are updatable

Creating Views

Simple View

CREATE VIEW employee_summary AS
SELECT 
    employee_id,
    CONCAT(first_name, ' ', last_name) AS full_name,
    department,
    salary
FROM employees;

Using the View

SELECT * FROM employee_summary
WHERE salary > 50000;

Explanation

View hides the actual table structure
Can be used just like a table
Simplifies query usage for end users

Complex View with JOINs

CREATE VIEW employee_details AS
SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name,
    d.location,
    e.salary
FROM employees e
INNER JOIN departments d 
ON e.department = d.department_name;

Use Case

Reporting systems
Dashboards
Multi-table data abstraction

View with Aggregations

CREATE VIEW department_stats AS
SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    SUM(salary) AS total_payroll
FROM employees
GROUP BY department;

Querying Aggregated View

SELECT * 
FROM department_stats
WHERE employee_count > 10;

Important Note

Aggregated views are read-only
Cannot be updated directly

Updatable Views

Example

CREATE VIEW high_earners AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 70000;

Updating Data Through View

UPDATE high_earners
SET salary = salary * 1.05
WHERE employee_id = 101;

Conditions for Updatable Views

A view is updatable only if it:

Refers to one table only
Does NOT use GROUP BY
Does NOT use aggregate functions
Does NOT use DISTINCT
Does NOT use UNION
Does NOT contain subqueries in SELECT
Does NOT include calculated columns

WITH CHECK OPTION

Ensures updated data still satisfies view condition.

CREATE VIEW high_earners AS
SELECT *
FROM employees
WHERE salary > 70000
WITH CHECK OPTION;

Benefit

Prevents updates that violate view rules

Modifying Views

Replace Existing View

CREATE OR REPLACE VIEW employee_summary AS
SELECT 
    employee_id,
    CONCAT(first_name, ' ', last_name) AS full_name,
    department,
    salary,
    hire_date
FROM employees;

Dropping Views

DROP VIEW employee_summary;

Viewing View Definition

SHOW CREATE VIEW employee_summary;

SELECT definition
FROM information_schema.views
WHERE table_name = 'employee_summary';

Types of Views

1. Simple View

Based on one table
Often updatable

2. Complex View

Uses joins, aggregations
Read-only

3. Inline View

Subquery in FROM clause
Temporary usage

4. Materialized View (Advanced)

Stores actual data
Improves performance
Needs refresh (DB-specific)

Views vs Tables

Feature	Table	View
Stores data	Yes	No
Takes storage	Yes	Minimal
Always updated	Depends	Yes
Can be indexed	Yes	No (mostly)
Can simplify queries	❌	✅

Views vs Stored Queries

Aspect	View
Reusable	Yes
Stored logic	Yes
Parameter support	No
Performance	Depends on query

Security Using Views

Restrict sensitive columns
Hide salary, personal info
Grant access only to view

GRANT SELECT ON employee_summary TO hr_user;

Performance Considerations

Views do not improve performance by default
Underlying query still executes
Poorly written views can slow queries
Index base tables for better performance

Common Mistakes with Views

Using views instead of proper indexing
Over-nesting views
Updating complex views
Assuming views store data
Ignoring performance impact

Log In

Sign Up