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:

sql
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:

sql
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:

sql
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:

sql
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:

sql
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:

sql
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:

sql
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:

sql
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:

sql
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:

sql
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!