💡 Ask Tutor

Chapter 20: Simplifying Queries with Views

As your database grows and your application becomes more complex, your SELECT queries can become very long and intricate, especially with multiple JOINs, WHERE clauses, GROUP BY aggregations, and subqueries. Re-writing these complex queries repeatedly can be tedious, error-prone, and difficult to manage.

This is where Views come in. A view is a virtual table based on the result-set of a SQL query. It doesn’t store data itself (except for materialized views, which are a more advanced topic not covered here); instead, it stores the query that produces the data. When you query a view, the database executes the underlying query and presents the result as if it were a regular table.

What is a View?

  • A view is a named, stored SELECT query.
  • It acts like a virtual table: you can SELECT from it, JOIN it with other tables or views, and in some cases, even INSERT, UPDATE, or DELETE data through it (though this has limitations).
  • Views simplify complex queries, making them appear as simple table selections.
  • They provide a layer of abstraction over the underlying tables.

Why Use Views?

  1. Simplification: Encapsulate complex JOINs and logic into a simple, single entity. Instead of writing a long JOIN query every time, you just SELECT * FROM my_complex_view;.
  2. Security: You can grant users access to specific views instead of directly to the underlying tables. This allows you to restrict which rows and columns they can see, without granting them full table access. For example, a view might expose customer names and order totals but hide sensitive details like email addresses or credit card numbers.
  3. Data Consistency & Abstraction: If the underlying table structure changes (e.g., a column is renamed), you can modify the view definition, and applications querying the view don’t need to change their code (as long as the view’s output remains consistent).
  4. Business Logic Encapsulation: Views can define specific business logic or derived data (e.g., total_sales_per_customer).

Updatable Views (Limitations)

While you can SELECT from virtually any view, INSERT, UPDATE, or DELETE operations on a view are generally restricted to simpler views (e.g., views based on a single table, without JOINs, GROUP BY, aggregate functions, DISTINCT, etc.). If a view is not updatable, you’ll need to modify the data directly in the underlying base tables.

SQL Syntax & Examples (MySQL/MariaDB)

Let’s continue using our scriptbuzz_db. Remember to run USE scriptbuzz_db; first.

1. Creating a View (CREATE VIEW)

Syntax:

SQL
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table1
JOIN table2 ON condition
WHERE condition
GROUP BY column;

Example 1: Create a view to show active products with their categories

This will simplify getting active products that have a category assigned.

SQL
CREATE VIEW active_categorized_products AS
SELECT product_id, name, price, category, stock_quantity
FROM products
WHERE is_active = 1 AND category IS NOT NULL;

What it does: Creates a view named active_categorized_products. When you query this view, it will execute the SELECT statement defined within it.

Using the View:

SQL
SELECT * FROM active_categorized_products;

Expected Output (example):

product_idnamepricecategorystock_quantity
1Laptop Pro1350.00Electronics49
2Wireless Mouse25.50NULL250
3Mechanical Keyboard99.99Electronics30
4USB-C Hub39.00Accessories100
7Gaming Monitor350.00Electronics20
8Gaming Mouse75.00NULL40

Oops! My example is_active status for Wireless Mouse and Gaming Mouse might be 1 but they have a NULL category. Let’s fix that or refine the view.

Let’s refine the view to show only truly categorized products. To verify, let’s see which products have NULL category: SELECT product_id, name, category FROM products WHERE category IS NULL; If ‘Wireless Mouse’ or ‘Gaming Mouse’ have NULL category, they won’t appear if category IS NOT NULL is in the view. This is fine.

Revised Example 1 (more precise): Create a view for active products with non-NULL categories

SQL
DROP VIEW IF EXISTS active_categorized_products; -- Drop if it exists for re-creation

CREATE VIEW active_categorized_products AS
SELECT product_id, name, price, category, stock_quantity
FROM products
WHERE is_active = 1 AND category IS NOT NULL;

Using the View:

SQL
SELECT * FROM active_categorized_products WHERE stock_quantity > 0;

Expected Output (example, after previous updates):

product_idnamepricecategorystock_quantity
1Laptop Pro1350.00Electronics49
3Mechanical Keyboard99.99Electronics30
4USB-C Hub39.00Accessories100
7Gaming Monitor385.00Electronics20

What it does: Now, only products that are active AND have a non-NULL category will appear when you query active_categorized_products.

Example 2: Create a view for customer order summaries (complex query simplified)

SQL
CREATE VIEW customer_order_summaries AS
SELECT
    u.user_id,
    u.username,
    u.email,
    COUNT(o.order_id) AS total_orders,
    SUM(o.total_amount) AS total_spent,
    MAX(o.order_date) AS last_order_date
FROM users AS u
LEFT JOIN orders AS o ON u.user_id = o.user_id
GROUP BY u.user_id, u.username, u.email;

What it does: This view encapsulates a LEFT JOIN and multiple aggregate functions. It provides a convenient way to get a summary of each user’s order history.

Using the View:

SQL
SELECT username, total_orders, total_spent FROM customer_order_summaries WHERE total_spent > 100;

Expected Output (example):

usernametotal_orderstotal_spent
john_doe4380.75
admin_user2150.25

2. Listing Views

To see existing views in your current database:

Syntax:

SQL
SHOW FULL TABLES IN database_name WHERE Table_Type = 'VIEW';
-- OR
SHOW TABLES LIKE '%view_name%'; -- Less specific, but works

Example 3: Show views in scriptbuzz_db

SQL
SHOW FULL TABLES IN scriptbuzz_db WHERE Table_Type = 'VIEW';

3. Dropping a View (DROP VIEW)

Syntax:

SQL
DROP VIEW [IF EXISTS] view_name;

Example 4: Drop the active_categorized_products view

SQL
DROP VIEW IF EXISTS active_categorized_products;

What it does: Removes the view definition from the database. It does not affect the underlying tables or their data.

4. Altering a View (ALTER VIEW)

Syntax:

SQL
ALTER VIEW view_name AS
SELECT new_column1, new_column2, ...
FROM new_table_structure
WHERE new_condition;

ALTER VIEW is used to change the underlying SELECT statement of an existing view.

Example 5: Alter customer_order_summaries to only include completed orders in sum

SQL
ALTER VIEW customer_order_summaries AS
SELECT
    u.user_id,
    u.username,
    u.email,
    COUNT(o.order_id) AS total_orders,
    SUM(CASE WHEN o.status = 'completed' THEN o.total_amount ELSE 0 END) AS total_spent_completed,
    MAX(o.order_date) AS last_order_date
FROM users AS u
LEFT JOIN orders AS o ON u.user_id = o.user_id
GROUP BY u.user_id, u.username, u.email;

What it does: Redefines the view. Now, total_spent_completed will only sum the total_amount for orders where the status is ‘completed’. (CASE WHEN is a conditional expression, we’ll cover it more directly in advanced topics, but here it says “if status is completed, use total_amount, else use 0”).

Common Pitfalls and Best Practices

  • Performance: Querying a view essentially means executing its underlying SELECT statement. A poorly optimized view (e.g., one that does full table scans or complex joins on large tables) will still be slow. Views do not inherently improve performance (unless it’s a materialized view, which MySQL doesn’t directly support).
  • Updatability: Be aware of the limitations of updatable views. For complex views, you’ll need to update the base tables directly.
  • Dependencies: Dropping or altering a base table column that a view relies on will break the view.
  • Naming Conventions: Use clear naming conventions for your views (e.g., vw_ prefix or _view suffix).
  • Security Model: Views are excellent for implementing a granular security model.

Notes:

  • A View is a virtual table based on the result-set of a SELECT query.
  • They simplify complex queries, provide security by restricting data visibility, and offer abstraction from underlying table changes.
  • CREATE VIEW defines a new view.
  • DROP VIEW removes a view.
  • ALTER VIEW redefines an existing view.
  • Views do not store data; they store the query definition.
  • While views can be queried like tables, their updatability is limited for complex definitions.

Small Exercise

Using your scriptbuzz_db:

  1. Create a view named high_value_products that includes product_id, name, price, and stock_quantity for all products where the price is greater than 100.00 and stock_quantity is greater than 0.
  2. Create a view named user_order_details that displays username, order_id, order_date, total_amount, and status for all orders, including users who have not placed any orders. (Hint: Use LEFT JOIN on users and orders).
  3. Query your high_value_products view to find all products with a stock_quantity less than 50.
  4. Drop the high_value_products view.

You’ve now added another powerful tool to your SQL arsenal for organizing and presenting your data! This concludes a very substantial section of core SQL. Next, we’ll quickly cover some more advanced DDL concepts, like users and permissions.

📘 Continue to the Next Chapter

Database Users & Permissions – GRANT & REVOKE

Understand how to manage database users securely by assigning or revoking access rights using SQL GRANT and REVOKE commands.

➡️

🚀 Explore Popular Learning Tracks