5.3 Views: Creating and Managing Views

Views in SQL are virtual tables that present data from one or more tables in a specific way, without physically storing that data. A view is essentially a saved SQL query that can be treated as a table for the purposes of querying. Views are useful for simplifying complex queries, securing data, and presenting data in a particular format without altering the underlying tables.

5.3.1 What is a View?

A view is a database object that provides a way to represent the result of a query. It does not store data itself but pulls data from one or more tables based on the query defined within it. Once created, a view can be queried just like a regular table.

For example:

• A view can aggregate sales data by region, so users can easily retrieve the total sales per region without writing complex SQL queries each time.
• A view can hide sensitive data (like employee salaries or customer personal information), providing access only to relevant information for certain users.

5.3.2 Creating Views

To create a view, you use the CREATE VIEW statement. A view can be created from a single table or from multiple tables using joins. Here is the basic syntax:

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

Example 1: Simple View

Suppose you have a table called employees and you want to create a view that shows only the names and positions of employees:

CREATE VIEW employee_positions AS
SELECT first_name, last_name, position
FROM employees;

You can now query the employee_positions view like a regular table:

SELECT * FROM employee_positions;

Example 2: View with Joins

You can also create views using joins to combine data from multiple tables. For example, if you have orders and customers tables and you want a view that combines them:

CREATE VIEW customer_orders AS
SELECT customers.customer_name, orders.order_id, orders.order_date
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;

Now, querying customer_orders will show the combined data from both the customers and orders tables:

SELECT * FROM customer_orders;

5.3.3 Managing Views

Once a view is created, you can perform several operations to manage it:

1. Viewing Existing Views

You can retrieve a list of all views in a database using the following query (for most DBMS systems):

SHOW VIEWS;

In PostgreSQL, you can use the following:

SELECT table_name FROM information_schema.views;

2. Updating Views

To modify an existing view, you can either drop and recreate the view or use the CREATE OR REPLACE VIEW statement. This will update the view definition without needing to drop the original one.

For example, if you want to change the employee_positions view to include the department name:

CREATE OR REPLACE VIEW employee_positions AS
SELECT first_name, last_name, position, department
FROM employees;

3. Dropping a View

To delete an existing view, use the DROP VIEW statement:

DROP VIEW view_name;

If the view does not exist, you’ll get an error unless you use the IF EXISTS clause:

DROP VIEW IF EXISTS employee_positions;

5.3.4 Benefits of Using Views

• Simplicity: Views allow you to encapsulate complex queries. You can create a view with a complex join and refer to it in simpler queries.
  Example: Instead of writing a complex join every time you want to retrieve customer orders, you can just query the customer_orders view.
• Security: Views can help in securing sensitive data. You can restrict access to only the columns or rows that a specific user needs to see.
  Example: A view can be created to show employee names and departments but exclude salary information.
• Data Integrity: Views provide a consistent interface to the underlying data. If the data changes (e.g., new rows are added to the tables), the view automatically reflects these changes without requiring you to modify the queries.
• Reusability: Once created, views can be reused multiple times in queries, reducing redundancy and ensuring consistency in how data is retrieved.

5.3.5 Types of Views

• Simple View: Created from a single table and does not include any complex calculations or aggregations.
  Example:

  CREATE VIEW employee_info AS
  SELECT first_name, last_name, department FROM employees;
  

• Complex View: Created from multiple tables, often involving joins, groupings, or subqueries.
  Example:

  CREATE VIEW department_sales AS
  SELECT departments.department_name, SUM(sales.amount) AS total_sales
  FROM departments
  JOIN sales ON departments.department_id = sales.department_id
  GROUP BY departments.department_name;
  

• Updatable View: A view that allows updates to the underlying tables via the view. This can only be done if the view does not contain complex features like joins or aggregations that would prevent updates.
• Materialized View: Unlike regular views, materialized views store a snapshot of the query result. This allows faster querying at the expense of not reflecting the latest changes to the underlying data unless manually refreshed.

5.3.6 Conclusion

Views are a powerful SQL feature that simplifies querying, enhances security, and abstracts complex data structures. They help encapsulate commonly used queries, provide a layer of abstraction between users and the actual database schema, and improve the security of sensitive data. By understanding how to create, manage, and use views effectively, you can optimize your database interactions and improve the performance and maintainability of your applications.