Views & Types of Views - MySQL

[akash-coded]

Views in MySQL are virtual tables that are used to represent the data of one or more tables in a more suitable format. They are just queries that are stored in the database, and the result of these queries is presented as a table.

Types of Views:

1. Updatable View: An updatable view is one which allows performing operations (INSERT, UPDATE, DELETE) on the view that affects the underlying tables.
2. Read-Only View: A read-only view is a view where we can only fetch the data but cannot update it.
3. Materialized View: Materialized views are like normal views but they save the result in the disk as a real table which can be updated periodically.

Use Cases and Examples from classicmodels database

Use Case 1: Simplifying complex queries

Suppose you have to repeatedly run a complex query that fetches detailed order information by joining orders, orderdetails, and products tables. This can be simplified using a view.

CREATE VIEW order_summary AS
SELECT o.orderNumber, o.orderDate, d.productCode, p.productName, d.quantityOrdered, d.priceEach
FROM orders o
JOIN orderdetails d ON o.orderNumber = d.orderNumber
JOIN products p ON d.productCode = p.productCode;

Now, whenever detailed order information is required, you can simply SELECT * FROM order_summary;

Use Case 2: Restricting access to data

If you want to provide access to order information but don't want to expose the price details, you can create a view that excludes price-related columns:

CREATE VIEW order_info AS
SELECT o.orderNumber, o.orderDate, d.productCode, p.productName, d.quantityOrdered
FROM orders o
JOIN orderdetails d ON o.orderNumber = d.orderNumber
JOIN products p ON d.productCode = p.productCode;

This view can be read-only to prevent changes to the underlying data.

Optimization, Best Practices and Do's and Don'ts

1. Use views judiciously: Views can help simplify your queries, but overuse can lead to performance issues. They add a layer of abstraction which can slow down query execution, particularly for complex views involving multiple joins and aggregations.
2. Prefer Read-Only views for data protection: Unless you need to modify the data, it's better to use read-only views to prevent accidental changes to the underlying tables.
3. Index the underlying tables: If you're creating a view on a large table or on a result of join of multiple tables, make sure the columns used in joining or in WHERE clause of view are indexed in the underlying tables.

Complex Interview Questions

1. How does MySQL handle updates to a view?
   MySQL handles updates to a view by translating them into updates of the underlying table. However, there are certain limitations and not all views are updatable. For example, views that use UNION, aggregate functions, or a subquery in the SELECT clause are not updatable.

2. What is the difference between a view and a derived table?
   A view is a virtual table stored in the database and can be used in multiple queries. A derived table is a subquery in the FROM clause of another query and only exists for the duration of that query.

3. How does the performance of a query on a view compare with a query on a base table?
   A query on a view could potentially be slower than a query on a base table, depending on the complexity of the view. Since a view is a stored query, when a query is made on a view, MySQL has to execute the query for the view and then execute the user's query on the result. However, in some cases, MySQL can merge the view's query with the user's query, resulting in performance that's similar to querying the base table directly.

Remember, the most important aspect of using views is that they help in organizing data in a user-friendly way. Views are very powerful, but with power comes responsibility, so they should be used judiciously.

Sure, let's explore the different types of views using examples from the classicmodels database.

1. Updatable View

An updatable view allows performing operations like INSERT, UPDATE, DELETE on the view that will affect the underlying tables.

Example: Let's create an updatable view based on the customers table.

CREATE OR REPLACE VIEW customer_view AS
SELECT customerNumber, customerName, phone, city, country
FROM customers;

Now, you can use this view to update the customers table.

UPDATE customer_view SET phone = '1234567890' WHERE customerNumber = 103;

Use Case: Updatable views can be useful when you want to allow users to modify data in certain columns but not others. For instance, in this case, a user might be allowed to update customer's contact information but not their customerNumber.

2. Read-Only View

A read-only view does not allow performing INSERT, UPDATE, DELETE operations. These views are usually used when you want to grant users access to specific data without giving them the ability to modify it.

Example: Let's create a read-only view based on the orders table.

CREATE OR REPLACE VIEW readonly_order_view AS
SELECT orderNumber, orderDate, status
FROM orders;

Use Case: Read-only views can be used to provide users with access to aggregated data for reporting purposes, without giving them the ability to change the underlying data.

3. Materialized View

MySQL does not natively support materialized views as of my knowledge cutoff in September 2021, but they can be emulated using a combination of stored procedures, triggers, and tables. A materialized view is a view where the query result is saved as a physical table that can be updated periodically.

Example: A materialized view emulated based on products and orderdetails tables, which aggregates the total quantity ordered for each product.

First, create a table:

CREATE TABLE materialized_product_order_view AS
SELECT p.productCode, p.productName, SUM(od.quantityOrdered) as totalQuantity
FROM products p
JOIN orderdetails od ON p.productCode = od.productCode
GROUP BY p.productCode, p.productName;

Then create a procedure to update it:

CREATE PROCEDURE RefreshMaterializedProductOrderView()
BEGIN
    DELETE FROM materialized_product_order_view;

    INSERT INTO materialized_product_order_view
    SELECT p.productCode, p.productName, SUM(od.quantityOrdered) as totalQuantity
    FROM products p
    JOIN orderdetails od ON p.productCode = od.productCode
    GROUP BY p.productCode, p.productName;
END

You can call CALL RefreshMaterializedProductOrderView(); to refresh the view.

Use Case: Materialized views are useful when you have complex queries on large tables that take a long time to execute. They trade off increased storage usage and some data staleness for increased query speed.

4. Inline Views

An inline view is a subquery in the FROM clause of another SQL statement.

Example: Let's use an inline view to find products that have orders exceeding a certain quantity.

SELECT iv.productCode, iv.productName
FROM
(SELECT p.productCode, p.productName, SUM(od.quantityOrdered) as totalQuantity
 FROM products p
 JOIN orderdetails od ON p.productCode = od.productCode
 GROUP BY p.productCode, p.productName) AS iv
WHERE iv.totalQuantity > 1000;

Use Case: Inline views can be used to simplify complex queries by breaking them down into smaller, more manageable parts. They are especially useful when the result of a subquery is needed in multiple places within the main query, or when a subquery needs to be used in a FROM clause.

Each type of view has its own use case and is used depending on the requirement. The key is to identify the right type of view for the right situation.

Views, Common Table Expressions (CTEs), and Subqueries are all useful constructs in SQL for organizing complex queries, but they each have their strengths and use cases.

Views

• Views are stored queries that act like virtual tables.
• They can be referenced in other queries, similar to how you'd reference a table.
• They can help encapsulate complex logic, simplify queries, and enhance security by restricting access to certain columns.
• Since views are stored in the database, they can be used across multiple sessions and by different users.
• However, every time a view is used, the underlying query gets executed. This can lead to performance issues if the view is based on a complex, resource-intensive query.

CTEs (Common Table Expressions)

• A CTE is a temporary result set that can be referenced within another SELECT, INSERT, UPDATE, or DELETE statement.
• They are typically used to simplify complex joins and subqueries, and to segment a query into more readable, modular parts.
• Unlike views, CTEs are not stored in the database and only exist for the duration of the query.
• Also, CTEs do not have the performance drawback of views because they are executed once and then stored in memory for the rest of the query execution.
• Recursive CTEs can be used for hierarchical or tree-structured data, a capability that isn't available with views or subqueries.

Subqueries

• Subqueries (or nested queries) are queries embedded within the WHERE or HAVING clause of another SQL query.
• They are used to return data that will be used in the main query to return the overall result set.
• Subqueries can be used in SELECT, INSERT, UPDATE, and DELETE statements wherever expressions are allowed.
• Unlike views, subqueries aren't stored in the database, and unlike CTEs, they don't simplify the reading of the query to the same extent.

When to use Views vs CTEs

1. Use Views when you have complex queries that will be used frequently. It provides a way to encapsulate and store these complex operations. Views can also be used when you want to restrict access to specific columns in tables.

2. Use CTEs when you have a complex query that you need to use only within a single query and won't need to reuse in multiple places. CTEs are great for breaking down complex queries into more manageable parts. Also, use CTEs when dealing with recursive tasks, such as traversing hierarchical data.

While views and CTEs have their unique strengths, the choice between using views or CTEs often comes down to the specific requirements of your task, the complexity of your query, and the need for stored, reusable queries versus temporary, single-query abstractions.

An inline view is a type of subquery used in the FROM clause of another SQL statement. Essentially, inline views are subqueries in the FROM clause that create a temporary table which can be used for the duration of the main query. On the other hand, a subquery is a query embedded within the WHERE, FROM, SELECT, or HAVING clause of another SQL query. It can also return a value or values which can be used in the main query.

Here's a simple way to distinguish: If your subquery is in the FROM clause, it can be called an inline view.

Let's consider examples using the classicmodels database.

Subquery:
A subquery in a WHERE clause might look like this:

SELECT customerNumber, checkNumber, amount
FROM payments
WHERE amount > 
    (SELECT AVG(amount) 
    FROM payments);

This will return the customerNumber, checkNumber, and amount from the payments table where the payment amount is greater than the average payment amount.

Inline View:
Here's an example of an inline view:

SELECT iv.customerNumber, COUNT(iv.checkNumber) AS numPayments
FROM 
    (SELECT customerNumber, checkNumber 
    FROM payments) AS iv
GROUP BY iv.customerNumber;

In this example, the inline view iv is used to create a temporary table of customerNumber and checkNumber from the payments table. The main query then uses this inline view to count the number of payments per customer.

In terms of when to use each, here's a basic guideline:

Subquery:

• Use a subquery when you want to use the result of one query in the WHERE or HAVING clause of another. They can also be used in the SELECT clause to perform calculations or in the FROM clause as an inline view.
• Subqueries can be useful when the results of the subquery are relatively small and can be easily used in the main query's WHERE clause to filter results.

Inline View:

• Use an inline view when you want to create a temporary table in the FROM clause for use in the main query.
• Inline views can be useful when you want to perform complex joins and aggregations. They also help simplify your SQL statements and make them more readable by breaking down complex operations into smaller parts.

As a rule of thumb, if your subquery needs to process a large amount of data or is used in multiple parts of the main query, it can be more efficient to use an inline view.

A materialized view is a database object that contains the results of a query that can be updated as needed from the original base tables. The "materialized" part means that the results of the query (the view) are computed and stored physically like a regular database table. In other words, when you query a materialized view, you are accessing pre-computed data, you're not running the original SQL against the base tables. This can significantly speed up query performance on complex queries or on large data sets.

Materialized views work by executing the SQL query once and then holding onto those results for later reference. The data in a materialized view can be updated from the base tables either manually or automatically, depending on how the materialized view is set up.

Manual Refresh: With a manual refresh, the data in the materialized view is not updated until a specific SQL command is executed to refresh the data. This is useful when you don't need real-time data and you want to control when the computational load of the refresh operation occurs.

Automatic Refresh: With an automatic refresh, the data in the materialized view is updated whenever a transaction commits altering the data in the base tables. This is useful when you need real-time or near real-time data in your materialized view.

To give a simplified example, let's imagine we have a sales table with millions of rows of data, and we often run a query that calculates total sales by region. Instead of running that computationally expensive query each time, we could create a materialized view of total sales by region. When data in the sales table changes, the total sales by region in the materialized view would be updated according to the refresh method (manual or automatic) that's been chosen.

It's important to note that as of my knowledge cutoff in September 2021, MySQL does not directly support materialized views, but you can effectively create one using a combination of stored procedures, triggers, and tables.

Remember:

1. Materialized views can significantly speed up query performance, but they also add computational load each time they are refreshed, which can impact performance.

2. Materialized views consume storage space, so they should be used judiciously, especially when working with very large databases.

3. The choice between manual and automatic refresh often comes down to a trade-off between needing up-to-date data versus managing computational load and performance.

4. The data in the materialized view is only as up-to-date as the last time the view was refreshed. If the base data changes, those changes are not reflected in the materialized view until it is refreshed.