arvb.net

SQL Views

2 min read

SQL Views are virtual tables based on the result of a SQL query. They do not store data themselves but provide an abstraction layer to simplify complex queries, enhance security, and improve maintainability.

Basic Syntax

CREATE VIEW name AS
SELECT column1, column2
FROM table
DROP VIEW IF EXISTS name
ALTER VIEW name RENAME to new_name

Examples

Example 1: Creating a Simple View

CREATE VIEW high_salary_employees AS
SELECT employee_id, name, salary
FROM employees
WHERE salary > 75000;

Explanation:

  • The high_salary_employees view filters employees earning more than $75,000.
  • It acts like a virtual table that can be queried just like a regular table.
SELECT * FROM high_salary_employees;

Example 2: Using Views for Joins

CREATE VIEW employee_department AS
SELECT e.employee_id, e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Explanation:

  • The view simplifies access to employee department information, reducing the need for repetitive joins in queries.
SELECT * FROM employee_department WHERE department_name = 'Engineering';

Example 3: Modifying a view

CREATE OR VIEW high_salary_employees AS
SELECT employee_id, name
FROM employees
WHERE salary > 10000;

To delete a view use the SQL Drop command on the view itself:

DROP VIEW IF EXISTS high_salary_employees

Explanation:

  • This will create a view as selected, or replace an existing one (based on the view name)

When to Use Views

Simplify Complex Queries – Encapsulate joins, aggregations, or filters into a reusable structure.
Enhance Security – Restrict access to specific columns or rows by exposing only necessary data.
Improve Maintainability – Abstract table structures so underlying schema changes don’t affect application queries.
Precomputed Aggregations – Use materialized views (if supported) for performance benefits.

Final Notes

  • Views behave like tables but don’t store data – they pull data dynamically from underlying tables.
  • Modifications through views: You can INSERT, UPDATE, or DELETE if the view meets certain conditions (e.g., no aggregates, no joins).
  • Dropping Views: Use DROP VIEW view_name; to remove a view without affecting the underlying data.

Graph View