
Contents
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
Feature | View | Table |
---|---|---|
Data Storage | Virtual (no data stored). For Materialized Views, data is stored. | Physical (stores data) |
Updateable | Sometimes | Always |
Indexing | Only Materialized Views support indexing. | Supported |
Performance | May re-run queries | Fast access |
Security | Can expose limited data | Full table access |
View support in popular Relational Database Systems
Feature | PostgreSQL | MySQL | SQL Server | Oracle | SQLite |
---|---|---|---|---|---|
Basic Views | ✅ | ✅ | ✅ | ✅ | ✅ |
Updatable Views | ✅ | ✅ | ✅ | ✅ | ✅ |
WITH CHECK OPTION | ✅ | ✅ | ✅ | ✅ | ❌ |
INSTEAD OF Triggers | ✅ | ❌ | ✅ | ✅ | ❌ |
Materialized Views | ✅ | ❌ | ✅ | ✅ | ❌ |
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!