🔧 Views in SQL

🕛 Zeit seit Veröffentlichung: 57 Tage, 13 Stunden 9 Minuten
📆 Veröffentlicht am: 10.10.2024 um 16:33 Uhr
Nachrichtenbereich: 🔧 Programmierung
🔗 Quelle: dev.to

Views in SQL

A view in SQL is a virtual table that is created based on the result set of a query. It doesn't store data physically in the database like a table does. Instead, it dynamically retrieves data from one or more tables (or even other views) whenever it is queried.

Views simplify complex queries, abstract the database structure, and enhance security by exposing only specific data to the user.

Key Features of Views

1. Virtual Table:

A view behaves like a table when queried, but it doesn't actually store data. Instead, it fetches data from the base tables dynamically.

2. Simplification:

Views are used to simplify complex queries. You can encapsulate complex SELECT statements inside a view, making it easier for users to retrieve data without worrying about the underlying logic.

3. Security:

Views can be used to limit access to certain rows or columns in a table, ensuring that sensitive data is not exposed. For example, users can be granted permission to query a view without granting access to the underlying table.

4. Data Abstraction:

Views provide a layer of abstraction. If the structure of the underlying table changes, the view can be modified accordingly without impacting the applications or users that query the view.

5. Logical Data Independence:

Views provide a way to present data in a format that is independent of how it is stored. You can change the underlying table schema without affecting the view or its consumers.

Syntax to Create a View

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

Example

Suppose you have a table called employees:

CREATE TABLE employees (
emp_id NUMBER,
emp_name VARCHAR2(50),
department VARCHAR2(50),
salary NUMBER
);

INSERT INTO employees VALUES (1, 'John Doe', 'IT', 50000);
INSERT INTO employees VALUES (2, 'Jane Smith', 'HR', 45000);
INSERT INTO employees VALUES (3, 'James Brown', 'IT', 60000);
INSERT INTO employees VALUES (4, 'Mary Johnson', 'Finance', 70000);
COMMIT;

To create a view showing only employees in the IT department:

CREATE VIEW it_employees AS
SELECT emp_id, emp_name, salary
FROM employees
WHERE department = 'IT';

Now, you can query the view just like a table:

SELECT * FROM it_employees;

Output:

Updating Data Through Views

If a view is based on a simple SELECT statement (single table, no joins or aggregations), it can also be used to insert, update, or delete data in the underlying table.

For example, updating the salary for employee John Doe using the view:

UPDATE it_employees
SET salary = 55000
WHERE emp_name = 'John Doe';

The employees table will now reflect the updated salary for John Doe.

However, complex views that use joins, aggregations, or derived columns may not allow such updates.

Benefits of Using Views

1. Data Security: You can hide specific columns or rows from the base table, showing only the necessary information.

2. Simplification of Queries: Views simplify complex queries by encapsulating them in a single object that can be queried like a table.

3. Data Integrity: Views can present consistent data even if the underlying table structure changes.

4. Reusability: Views can be reused in different parts of the database, avoiding the need to rewrite complex queries.

Types of Views

1. Simple View: Based on a single table and does not contain complex logic (joins, aggregations, etc.).

Example:

CREATE VIEW simple_view AS
SELECT emp_name, salary FROM employees;

1. Complex View: Based on multiple tables or contains joins, subqueries, or aggregations.

Example:

CREATE VIEW employee_summary AS
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Limitations of Views

1. No Physical Data Storage: Since views are virtual, they don't store data physically. As a result, querying a view can sometimes be slower than querying a materialized view because it needs to dynamically retrieve the data from the underlying tables.

2. Performance: If a view contains complex logic or joins across large datasets, querying it can be slower because each time the view is queried, the underlying SELECT statement must be executed.

3. Cannot Always Be Updated: Complex views with joins, aggregations, or derived columns cannot be directly updated. Only simple views based on a single table can allow updates, inserts, and deletes.

Difference Between Views and Materialized Views

Conclusion

Views are a powerful feature in SQL for simplifying complex queries, enhancing security by restricting access to sensitive data, and providing a layer of abstraction. They are essential for improving database design, usability, and security but may have performance limitations compared to materialized views in specific scenarios.

...