The Developer Space

6 0

Introduction

Views are one of the most powerful and underutilized features in SQL. A view is essentially a virtual table based on the result of a SELECT query. It allows you to encapsulate complex queries and present them as simple tables, making code more maintainable and secure.

This article introduces SQL Views for beginners, explains how they differ from tables, covers platform-specific behavior, and explores related concepts like Materialized Views.

Articles in the Series

Before reading this article, I recommend that you read the previous articles in this series to understand the basics and to set up the Sample database that we’ll be using for the examples in the article.

SQL for Beginners: Learn the Basics with Examples

SQL for Beginners – Part 2: SELECT Commands with Examples

SQL for Beginners – Part 3: JOINS

SQL for Beginners – Part 4: Mastering Subqueries

SQL for Beginners – Part 5: VIEWS

Setting up the Sample Database

The examples in these article series were created using PostgreSQL (Database) and DbStudio (IDE). However, you can try out these examples in any Relational Database. You may have to tweak the syntax to make it work with your target database platform.

We created the Users and Orders tables in the previous articles (scroll down to the bottom). We’ll reuse these tables for code examples in this article.

Access code samples in this article here: https://github.com/The-Developer-Space/CodeSamples/blob/main/SQL_for_Beginners

What is a View?

A view is a named SQL query stored in the database. When you query a view, it behaves like a table, but it does not store data physically. Instead, it retrieves data dynamically from the underlying tables.

Why Views?

Simplicity

Views simplify complex queries by encapsulating them into a reusable format. This is especially useful when dealing with multiple joins or nested queries. Instead of rewriting the same logic in every query, you can define it once in a view.

Reusability

A view can be referenced in multiple queries, reports, or procedures. This improves consistency and reduces the risk of errors, especially when the underlying logic is complex and reused often.

Security

You can grant users access to the view without exposing the full underlying table, thus enhancing data security and compliance. Views can restrict access to specific columns or rows within a table.

Abstraction

Views abstract the underlying schema, so changes to the table structure (e.g., column renaming or splitting tables) can be managed in the view without impacting client applications or queries that depend on it.

Creating Views

To create a View, use the CREATE VIEW statement.

Syntax

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

Example

Let’s use our Users table from the previous articles and and create a View that returns only the Active Users.

CREATE VIEW ActiveUsers AS
SELECT UserID, FirstName, LastName, Email
FROM Users
WHERE IsActive = TRUE;

The ActiveUsers view abstracts the filter logic. You can now query it like a table. Now, you can use it anywhere in your queries to return only the active users, just like a table.

--Query the view:
SELECT * FROM ActiveUsers;

Result

Creating a view from more than one table

Views can also be created on multiple tables. For example, the following code shows how to create a User Order Summary view from the Users and Orders table. The AS keyword assigns a column name for computed columns.

CREATE VIEW UserOrderSummary AS
SELECT U.UserID, U.FirstName, U.LastName, COUNT(O.OrderID) AS OrderCount, SUM(O.Amount) AS OrderTotal
FROM Users U
INNER JOIN Orders O ON U.UserID = O.UserID
GROUP BY U.UserID, U.FirstName, U.LastName;

--Query the view
SELECT * FROM UserOrderSummary;

Result

Creating a view from other views

A view can also be created based on other views or a mix of table and views. The following examples creates the same User Order Summary but instead of the User table, it queries from the ActiveUsers view.

CREATE VIEW ActiveUserOrderSummary AS
SELECT U.UserID, U.FirstName, U.LastName, COUNT(O.OrderID) AS OrderCount, SUM(O.Amount) AS OrderTotal
FROM ActiveUsers U
INNER JOIN Orders O ON U.UserID = O.UserID
GROUP BY U.UserID, U.FirstName, U.LastName;

--Query the view
SELECT * FROM ActiveUserOrderSummary;

Result

Altering Views

If you need to modify a view (e.g., change its SELECT query or add/remove columns), you can use the CREATE OR REPLACE or ALTER VIEW command, depending on the RDBMS. Altering a view involves changing the entire view definition, and cannot add or remove individual columns.

Example (PostgreSQL/Oracle):

CREATE OR REPLACE VIEW ActiveUsers AS
SELECT UserID, FirstName, LastName, Email, PhoneNumber
FROM Users
WHERE IsActive = TRUE;

Example (SQL Server/MySQL/MariaDB):

ALTER VIEW ActiveUsers AS
SELECT UserID, FirstName, LastName, Email, PhoneNumber
FROM Users
WHERE IsActive = TRUE;

Deleting Views

To delete a view, use the DROP VIEW statement.

Syntax:

DROP VIEW view_name;

Example:

DROP VIEW ActiveUsers;

Note: Dropping a normal view does not delete the underlying data. It only removes the view definition. When a materialized view is dropped, its definition and data are dropped.

Updatable Views

Views are generally meant for querying only. However, Views can be updated if they meet certain conditions:

• The view must be based on a single table
• The columns should not be calculated columns.
• No GROUP BY, DISTINCT, UNION, or aggregate functions

Example:

CREATE VIEW EditableUsers AS
SELECT UserID, FirstName, LastName, Email FROM Users;

UPDATE EditableUsers
SET FirstName = 'John II'
WHERE UserID = 1;

--Verify update
SELECT * FROM EditableUsers WHERE UserID = 1;

Some databases require INSTEAD OF triggers to handle updates on views.

Materialized Views

A Materialized View stores the result of the query physically. Unlike standard views, the data is not dynamic and must be refreshed manually or periodically. Querying materialized views returns data stored as part of the view and not from the underlying tables.

Syntax (PostgreSQL/Oracle):

CREATE MATERIALIZED VIEW UserSummary AS
SELECT UserID, COUNT(*) AS OrderCount
FROM Orders
GROUP BY UserID;

Refreshing Materialized Views

When a Materialized View is created, a copy of the data returned by the view query is saved as part of the view. The data is not automatically updated when the data in the underlying tables change.

-- PostgreSQL
REFRESH MATERIALIZED VIEW UserSummary;

Real-world Use Cases

Dashboard and Analytics Reporting: A business intelligence dashboard showing total sales, average order value, customer segmentation, and top-selling products.

Slow-Moving Historical Data: Generating weekly reports from a transaction log that grows daily.

Search Optimization for E-commerce: Product search across categories, tags, reviews, and stock status.

Snapshotting External Data Sources: Syncing a subset of a remote database (e.g., customer data from a CRM or external API).

Data Warehousing and ETL: ETL pipelines for nightly data consolidation in a data warehouse.

Differences Between Views and Tables

View support in popular Relational Database Systems

Best Practices

• Prefix views with vw_ or use a naming convention to differentiate them from tables in queries
• Avoid excessive nesting in views
• Use WITH CHECK OPTION to ensure data integrity on updatable views
• Document the logic within views for future maintenance
• Use Materialized views with caution as they take up space

Conclusion

Views provide a clean and secure way to simplify your SQL queries. They are invaluable for abstraction, reusability, and performance optimization when combined with Materialized Views. Understanding how and when to use views can make you a more effective and efficient SQL developer.

Stay tuned for more articles in this series!