Well-designed applications usually provide an open interface, hiding implementation details, which allows for design changes without affecting end users.

When developing your database, you can achieve a similar result by encapsulating tables and providing access to data only through a set of views.

In this article, we will discuss what views are, how they are created, and what they can be useful for.

What is a view

A view is a database object that is the result of executing a query on the database, defined using the SELECT statement, at the time of accessing the view.

Views are sometimes called "virtual tables." This is because a view appears to the user as a table, but actually does not store data, but retrieves it from other tables at the time of access.

If the data in the underlying table changes, the user receives up-to-date data when accessing the view that uses this table. Views do not cache query results during operation.

Example of creating a view

As a simple example, suppose you want to partially hide email addresses in the user table (Users).

This can be useful, for example, if your company's policy does not allow everyone to use confidential user information. Therefore, instead of allowing direct access to the user table (Users), you define a view named ViewUsers and require everyone to use it to access user data.

Here is an example of defining this view:

    SELECT id,
           CONCAT(SUBSTR(email, 1, 2), '****', SUBSTR(email, -4)) AS email
FROM Users;

The view in an SQL query appears and is used like a regular table.

SELECT * FROM ViewUsers;
1Bruce Willisba****.com
2George Clooneyte****.com
3Kevin Costnerme****.com
4Donald Sutherlandra****.net
5Jennifer Lopezba****.com
6Ray Liottajd****.net
7Samuel L. Jacksonmo****.com
8Nikole Kidmanok****.com
9Alan Rickmanka****.net
10Kurt Russellga****.com
11Harrison Fordko****.com
12Russell Crowegl****.net
13Steve Martinne****.com
14Michael Cainedm****.com
15Angelina Joliech****.net
16Mel Gibsonro****
17Michael Douglasti****.com
18John Travoltawa****.com
19Sylvester Stalloneko****.com
20Tommy Lee Jonessz****.com
21Catherine Zeta-Jonesfl****.com
22Antonio Banderassa****.net
23Kim Basingerja****.net
24Sam Neillcl****.com
25Hideo Kojimaad****.org
26ClINT Eastwoodfr****.net
27Brad Pittke****.net
28Johnny Deppcg****
29Pierce Brosnantr****.com
30Sean Conneryjs****.com
31Bruce Williske****.com
32Mullah Omarjg****.com
33Vasanta Robertaro****.com

If you want to find out which columns are available in the view, you can use the DESCRIBE statement.


General syntax of a view

VIEW view_name [(view_column_names)]
AS select_expression

OR REPLACE — When using this optional parameter, if a view with the same name already exists, the old view will be deleted and a new one will be created. Otherwise, if you try to create a view with an existing name, an error will occur.

Why are views needed

Simplifying complex queries

Views are used to simplify complex queries and create an abstraction between the user and the database. They can hide the complexity of data structures and provide a simplified interface for accessing data.

Improving performance

Creating views that encapsulate complex queries can help optimize the execution of these queries. This can lead to faster query execution and overall improvement in database performance.

Ensuring security

Views can be used to ensure the security of confidential data. Creating views that restrict access to specific columns or rows of data allows administrators to limit access to sensitive information. This helps ensure that only authorized users have access to confidential data.


Views are an important tool in SQL that allows for simplifying complex queries, standardizing data access, improving performance, and ensuring data security

Let's check how well you understood the topic: choose the correct statement for the question "What is a view in a database?"