View in SQL
- A view in SQL is a virtual table that comes into being by running a question on a single or a few tables in a database.
- A view is a logical table that is created as a result of the execution of a query on the tables.
- In contrast to a physical table, a view is a representation of the data stored in the database without having the data itself.
- It only views data stored in the underlying tables, in a manner that data can be queried, updated, and used in a way similar to utilizing a standard table.
- While databases contain views, these views provide other benefits in the domains of data abstraction, security, and the ability to write complex queries in a simpler manner.
- This guide gives you a detailed walkthrough of SQL’s views, how they are organized, how they are made, the pros, and a few down-to-earth examples that anybody can follow so they are able to work with SQL databases effectively.
What is a View in SQL?
- A view in SQL can be used to increase security, simplify complex queries, and customize the display of data.
- It is practically a simulated table through which you can perform JOIN, still, it does not store any data in the physical way.
- Rather, a view receives a part of the data from one or more of the base tables or other views.
- During query execution, the view itself is replaced with the query underlying it, and the query result set is returned dynamically.
- As a view is a fundamental part of a SQL query by means of a SELECT statement, it may be of any degree of complexity that needs to be considered in the form of filtering, joining, aggregating, and other SQL operations.
Syntax for Creating a View in SQL
- VIEW creation is made by utilizing CREATE VIEW command. The simple structure is as follows:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
- Example:
- Take it for instance that we have employees and departments comprising two tables each.
- Here we are seeing the employees and the names of the department list.
- With the SQL below, the command to make the view would be:
CREATE VIEW employee_department_view AS
SELECT employees.name, employees.position, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id;
- Now, employee_department_view is a view containing data from the employees and departments tables, and you can access it exactly like a table with the following query:
SELECT * FROM employee_department_view;
Benefits of Using View in SQL
1.Data Abstraction:
- Views are a means to insulate your users from the complications of writing complex queries.
- By building a “view,” developers can hide the SQL queries complexity and create a uniform pattern that users will interact with regardless of the data source.
- For example, if you usually join two or more tables to get the data, you can make a view, the one that does these joins, and the users can retrieve the view without knowing the specific table structure.
2.Security and Access Control:
- In other words, a view allows to restrict data access. You can define a view and show your kids a certain number of items or lines from the associated tables exclusively. It makes it easier to manage the user’s access and keep data secure.
- For example, if you have employees’ table which consists of sensitive data such as salary, you can exclude the column of salary using a view to make the employees access only the non-sensitive data.
3.Simplified Querying:
- By building cores of tough queries through the use of views you can make your SQL code clean and simple to maintain. Clients can make effortless SELECTs on a view instead of entering complex SQL each time.
- Example: Instead of writing a complex join and filtering logic in multiple queries, you can create a view that encapsulates this logic, allowing for simpler queries.
4.Performance Optimization:
- Sometimes, performance can be enhanced through the usage of views, especially if the views are used to cover complex calculations or aggregations.
- However, they are not primary storage objects. On the other hand, indexing views are real tables and they can be much faster in query executing too.
5.Consistency and Reusability:
- Views guarantee that queries produce the same results. Just by making one change, one-time, to the view definition, you can make all the queries that use the view display the new information automatically.
- As a result, this reduces duplicate procedures and the probability of incorrect queries in the entire system.
Types of Views
1.Simple Views:
- Simple views are those that are based on a simple standard table and do not contain any complex joins, groupings or even aggregations. They are easy to create and use.
CREATE VIEW simple_view AS
SELECT name, age FROM employees WHERE age > 30;
This view will give you back the employees who are older than 30.
2.Complex Views:
- Complex views usually imply multiple tables which are usually joined, aggregated, or subjected to any other SQL operations. For these views, a lot of advanced manipulations can be performed with data.
CREATE VIEW complex_view AS
SELECT e.name, e.position, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 50000;
The view is a unification of the employees and departments tables and it describes employees with the salary higher than 50,000.
3.Materialized Views:
- Materialized views, as opposed to regular views, reside the result of the query physically.
- This can cause performance to be better by saving the results of complex queries to the hard drive although they have larger storage space.
- The Materialized views should be that updated from time to time countering the stored data that has gone mammoth.
CREATE MATERIALIZED VIEW employee_salary_summary AS
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
Materialized views are applicable to creating reports in applications where data is rarely updated.
Modifying and Dropping Views
1.Modifying a View: If one needs to tweak an already existing view, there are two possible directions to take:
- Delete and create it again with new definitions.
DROP VIEW view_name;
CREATE VIEW view_name AS SELECT ...;
- Some databases (like PostgreSQL) let you use the CREATE OR REPLACE VIEW command to alter a view without deleting it:
CREATE OR REPLACE VIEW view_name AS SELECT ...;
2.Dropping a View: A DROP VIEW statement is used to withdraw the view:
DROP VIEW view_name;
This results in the removal of the view, but without taking any effect on the actual database tables.
Limitations of SQL Views
- Performance Overhead: As views do not store data, querying a view entails running the underlying query every time it is accessed. So the overhead to performance can be increased, above all where clusters of data are concerned.
- Regular views do not allow direct indexing. Given that, in some database systems, a materialized view can be indexed for a better performance.
- Update Restrictions: Not all views are updatable. For example, if a view includes complex operations such as joins, aggregation, or subqueries, then it might be the case that a user cannot update the data through that view. For instance, some databases have particular laws and regulations on which views can be updated.
- Dependency on Base Tables: The view could become dysfunctional if the structure of the base tables (e.g., column names or data types) changes. So, you will have to take the necessary precautions when modifying base tables.
Practical Examples of SQL Views
- Employee View: Create a view to display the names of the employees and the department they belong to.
CREATE VIEW employee_department_view AS
SELECT e.name, e.position, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
- Department Summary: Create the view representing the total salary of each department.
CREATE VIEW department_salary_summary AS
SELECT d.department_name, SUM(e.salary) AS total_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;
- Student Grade View: Create a view to show the student names and their average grades.
CREATE VIEW student_grade_view AS
SELECT s.name, AVG(g.grade) AS avg_grade
FROM students s
JOIN grades g ON s.student_id = g.student_id
GROUP BY s.name;