In SQL, a view is a virtual table that is constructed by querying data from one or more tables in the database. Views allow you to simplify complex queries, improve performance, and provide a level of abstraction that hides the complexity of the underlying data. Views are particularly useful for creating reusable query templates, encapsulating logic, and restricting access to sensitive data.
An SQL view is essentially a stored query that can be treated as a table. It doesn’t store any data itself; instead, it stores the SQL query that defines the view. When you query a view, the SQL engine executes the underlying query and returns the result as if it were a table.
Key Features of SQL Views:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
view_name: The name of the view you want to create.SELECT statement: The query that defines the data the view will return.Let’s say you have a table called employees and you frequently need to get the names and salaries of employees who work in a particular department. Instead of writing the query every time, you can create a view for it.
CREATE VIEW employee_salaries AS
SELECT first_name, last_name, salary
FROM employees
WHERE department_id = 3;
Explanation:
employee_salaries will return the first_name, last_name, and salary of all employees who belong to department 3.Once a view is created, you can query it just like you would a regular table:
SELECT * FROM employee_salaries;
This query will return the list of employees in department 3 along with their salaries, based on the logic defined in the view.
SQL views can be modified or dropped if needed.
To modify an existing view, you can use the CREATE OR REPLACE statement, which allows you to change the view without dropping it first:
CREATE OR REPLACE VIEW employee_salaries AS
SELECT first_name, last_name, salary, hire_date
FROM employees
WHERE department_id = 3;
This updated view now includes the hire_date column in the result set.
If you no longer need a view, you can remove it using the DROP VIEW statement:
DROP VIEW employee_salaries;
SQL views can be categorized based on their usage and behavior. The main types are:
A simple view is one that is created using a single table and doesn’t contain complex operations like joins, groupings, or aggregations. It directly maps to the underlying table’s data.
CREATE VIEW simple_employee_view AS
SELECT first_name, last_name, department_id
FROM employees;
A complex view is one that is created by joining multiple tables, using aggregate functions, or applying filters. These views help simplify queries that require multiple operations.
CREATE VIEW department_salary_summary AS
SELECT d.department_name, AVG(e.salary) AS average_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;
Explanation:
An updatable view allows you to modify the data of the underlying tables through the view. To be updatable, the view must not contain operations such as aggregation, joins, or subqueries that would make it impossible to update the underlying data.
CREATE VIEW employee_details AS
SELECT first_name, last_name, salary
FROM employees;
You can update the data in the employees table through the employee_details view:
UPDATE employee_details
SET salary = 80000
WHERE first_name = 'Alice' AND last_name = 'Johnson';
This query will update the salary of the employee named Alice Johnson in the employees table.
A non-updatable view cannot be used to modify the data of the underlying tables. These views often contain complex logic, like joins, aggregation, or filtering, which makes it impossible to determine how changes should be propagated.
CREATE VIEW department_salary_summary AS
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
This view calculates the average salary per department and cannot be used to update the employees table.
Views allow you to encapsulate complex logic and present a simplified interface to users or applications. For example, rather than having to write a complicated JOIN query repeatedly, you can create a view and reference it whenever needed.
By using views, you can control which columns or rows of a table are accessible to users. You can create a view that exposes only specific columns, keeping sensitive data hidden.
CREATE VIEW employee_public_info AS
SELECT first_name, last_name, department_id
FROM employees;
In this example, you can allow users to access only the first_name, last_name, and department_id columns through the employee_public_info view, while keeping the salary and hire_date columns hidden.
Views can help improve performance by allowing the database to store optimized query plans. While views don’t physically store data, some database systems can materialize views, making it faster to access pre-aggregated or pre-joined data.
Once a view is created, you can reuse it in multiple queries, saving you time and effort. This also ensures consistency in your queries, as you’re reusing the same logic.
While SQL views are extremely useful, they come with some limitations:
INSERT, UPDATE, or DELETE unless the view is updatable.