Getting Started With Database Views in SQL

What Are Database Views?

A database view is a virtual table that contains data from one or multiple database tables.
Unlike a physical table, a view does not store data itself.
It contains a set of predefined SQL queries to fetch data from the database.
In other words, view is a uniquely named object in the database that stores a SQL select query for repeatable data access.

All SQL queries from this blog post were tested in the Postgres database. While the same SQL syntax can be used in other databases.

Benefits of Using Views

• Simplification of complex queries - views can encapsulate complex queries, allowing users to access the results through simple SELECT statements.
• Less repeatable select queries - views encapsulate select queries that can be repeatedly used in the data access code.
• Data abstraction - views provide a level of abstraction, allowing changes in underlying table structures without affecting how the data is presented to the end-users.
• Enhanced security - views enhance security by protecting data, as they can limit access to certain rows and columns of the table, instead of giving access to the whole table.

How to Create a View

To create a view in the database, you can use the CREATE VIEW SQL statement:

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

Let's set up a database in PostgreSQL that includes tables for customers, products, orders, and order_details:

CREATE TABLE customers (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(255),
    phone VARCHAR(20),
    email VARCHAR(255),
    is_active BOOLEAN
);

CREATE TABLE products (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(255),
    price DECIMAL
);

CREATE TABLE orders (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    customer_id INT,
    date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_details (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

You can use regular SELECT statement to get all customers:

SELECT * FROM customers;
-- OR
SELECT id, name, phone, email, is_active FROM customers;

Instead of repeating this query each time, we can define a database view that returns frequently used information about active customers:

CREATE VIEW customer_info AS
SELECT id, name, email, phone
FROM customers
WHERE is_active = true;

To retrieve data from the view you need to use view's name in the SELECT statement as a table name:

SELECT * FROM customer_info;
-- OR if you need less fields
SELECT id, name, email FROM customer_info;

When selecting data from the view you can use all SELECT statement features that you can use when selecting data from the real database table.

For example, you can use grouping to select unique customer names from the view:

SELECT name FROM customer_info
GROUP BY name;

How to Update and Delete view

Database allows you to recreate a view with an updated SELECT and WHERE statements. Use the CREATE OR REPLACE VIEW SQL command:

CREATE OR REPLACE VIEW customer_info AS
SELECT id, name, email, phone
FROM customers

However, you can only add and can't remove columns from the SELECT statement.
To completely change the view, you need to delete it from the database using the DROP VIEW SQL command:

DROP VIEW IF EXISTS customer_info;

Database Views for Complex Queries

Views are particularly useful for creating complex SQL queries or presenting aggregated data from multiple tables.

Before creating a view, I recommend first to create a SQL query, test it and only after that create a view.
This will reduce the time for recreating or even deleting a view before it was tested.

Let's create a view that returns the total sales per customer:

CREATE OR REPLACE VIEW total_sales_per_customer AS
SELECT customer.name, SUM(order_details.quantity * order_details.price) AS total_sales
FROM customers customer
JOIN orders "order" ON customer.id = "order".customer_id
JOIN order_details order_details ON "order".id = order_details.order_id
GROUP BY customer.name
ORDER BY total_sales desc;

-- SELECT data from the view
select * from total_sales_per_customer;

And a view that returns the top-10 frequently sold products:

CREATE OR REPLACE VIEW top_sold_products AS
SELECT product.name, COUNT(orderDetails.quantity * orderDetails.price) AS total_sales
FROM order_details orderDetails
JOIN products product ON orderDetails.product_id = product.id
GROUP BY product.name
ORDER BY total_sales desc
LIMIT 10;
-- in MS SQL Server use "SELECT TOP 10" to get top-10 rows
-- in Oracle use "WHERE ROWNUM <= 10;" with a select subquery to get top-10 rows

-- SELECT data from the view
select * from top_sold_products;

Summary

A database view is a virtual table that contains data from one or multiple database tables.
Unlike a physical table, a view does not store data itself, it provides an interface for repeatable data access.

Database views are a powerful feature in SQL, offering data abstraction, simplification of queries, reduction of repeatable queries and enhanced security through controlled data access.

Hope you find this blog post useful. Happy coding!

Originally published at https://antondevtips.com.

After reading the post consider the following:

• Subscribe to receive newsletters with the latest blog posts
• Download the source code for this post from my github (available for my sponsors on BuyMeACoffee and Patreon)

If you like my content —  consider supporting me

Unlock exclusive access to the source code from the blog posts by joining my Patreon and Buy Me A Coffee communities!