What is an SQL View?

A view is a well-known feature in SQL. It allows you to create a virtual table based on an SQL query referring to other tables in the database. A view stores an SQL query that is executed whenever you refer to the view. This is a convenient way to get the desired data because it is easier to run a query stored in a view than to type a query from scratch. Hence, it is essential to be able to apply this feature efficiently.

This article introduces the fundamentals of SQL views. First, we’ll go through the definition, usage, and working principles of a view. Then, we’ll dive into examples of how to create, modify, and drop a view. At the end of the article, you’ll find real-world application examples of SQL views. In some situations, using a view is inevitable, and we’ll discuss this in detail.

Diving Into SQL Views

To put it simply, a view is a stored SQL query. Every time a view is used, it executes its stored query and creates a result set consisting of rows and columns.

An SQL view is called a virtual table because it does not store the rows and columns on the disk like a concrete table. Instead, it just contains the SQL query. Let’s look at the diagram below to get a better grasp of what a view is.

The diagram above presents a view called EntertainmentView. It stores a simple query that selects the Name columns from the Theater, Opera, and Cinema tables. When this EntertainmentView is used (executed), it returns a result set consisting of a column AllEntertainmentPlaces with rows from all three tables.

To create the EntertainmentView view presented above, we would use the following syntax:

CREATE VIEW EntertainmentView AS
SELECT Name AS AllEntertainmentPlaces 
FROM Theater
UNION
SELECT Name 
FROM Oper
UNION
SELECT Name 
FROM Cinema;

After creating EntertainmentView, we can now run the following query instead of typing out the query above:

SELECT *
FROM EntertainmentView;

SQL views are commonly used in relational databases. Relational databases offer various features, including SQL constraints like primary and foreign keys or indexing. These features can lead to complex queries.

Hence, it is handy to create views to store complex queries. You can use a view as if it were a table storing the data returned by a complex query.

In case you missed our articles on SQL Constraints, Primary Keys, and Foreign Keys, you can catch up by visiting these links!

How To Use Views in an SQL Query

Although SQL views are considered virtual tables, their usage does not differ from concrete tables. You can use a view just like any other table.

Let’s look at an example using a view called FriendView that is built on top of the Person table. See the Person table below:

To create the FriendView view, we would use the following query:

CREATE VIEW FriendView AS
SELECT person.Id as Id, person.Name as Name, friend.Name as Friend
FROM Person person
JOIN Person friend
ON person.FriendId = friend.Id;

Then, we could run a query that treats the FriendView view like a normal table as shown below:

SELECT Name, Friend
FROM FriendView
WHERE Name LIKE 'F%';

The query above outputs two columns from the FriendView view. The rows included in the result set must fulfill the WHERE clause condition indicating that the person’s name must start with the letter “F.”

How Does an SQL View Work?

In the previous section, we learned that an SQL view can be treated like a normal table. However, under the hood, there is a difference between views and tables.

A table (concrete table) stores its data in columns and rows in the database. A view (virtual table) is built on top of the concrete table(s) it fetches data from and does not store any data of its own in the database. A view only contains the SQL query that is used to fetch the data.

To summarize, the result set of a view is not materialized on the disk, and the query stored by the view is run every time we call the view. Let’s look at the diagram below to understand the difference between concrete and virtual tables.

Let’s Create Some SQL Views

Now that we know what SQL views are and how to use them, let’s look at the syntax for creating views in SQL. See the basic query below:

CREATE VIEW view_name AS
SELECT columns_list
FROM tables_list;

The CREATE VIEW command creates a view named view_name. The AS clause is followed by an SQL query that will be stored in a view.

Now, let’s look at an example that uses the Food and Animals tables to create the AnimalFoodView view. See the Food and Animals tables below:

Let’s create a view called AnimalFoodView based on the tables defined above:

CREATE VIEW AnimalFoodView AS
SELECT a.AnimalType, f.FoodName
FROM Animals a
JOIN Food f
ON a.FoodId=f.Id;

Now that the view is created, we can treat it as a normal table. Please note that the view will generate its result set only when its name is called as shown in the sample query below:

SELECT * from AnimalFoodView;

The output below is equivalent to the output of the query stored in the view:

There could be a situation in which you want to create a view or replace it if it already exists. The syntax below shows a way to do this:

CREATE OR REPLACE VIEW view_name AS
SELECT columns_list
FROM tables_list;

Let’s replace our AnimalFoodView such that it would show only Fish and Bird rows.

CREATE OR REPLACE VIEW AnimalFoodView AS
SELECT a.AnimalType, f.FoodName
FROM Animals a
JOIN Food f
ON a.FoodId=f.Id
WHERE AnimalType='Fish' OR AnimalType='Bird';

The original query that was saved in AnimalFoodView is now replaced with the above query. Let’s run another SELECT statement to make sure the replacement of the view took place:

SELECT * from AnimalFoodView;

The output below is again equivalent to the output of the query stored in the view:

With training and practice, it’s easy to work with SQL views. Our Working with Views course is a great way to do this!

What Else Can Be Done with SQL Views?

Modifying a View

It is also possible to modify a view after it is created. Let’s use the AnimalFoodView view defined in the previous section. If we want to use the AnimalId column instead of the AnimalName column, we can use an ALTER VIEW statement as follows:

ALTER VIEW AnimalFoodView AS
SELECT a.AnimalId, f.FoodName
FROM Animals a
JOIN Food f
ON a.FoodId=f.Id;

Now, the SELECT statement returns the animal IDs instead of the names.

SELECT * from AnimalFoodView;

If the view exists in the database, the CREATE OR REPLACE VIEW and ALTER VIEW commands have the same effect.

Dropping a View

If you want to delete a view, simply use the DROP VIEW command as shown below:

DROP VIEW AnimalFoodView;

After executing the DROP VIEW command, the view does not exist in the database anymore.

When Should You Use an SQL View?

Scenario I

One of the simplest reasons to use an SQL view is when you have a long and complex query. To save time on retyping and running the query, you can store it in a view and simply run the SELECT statement on that view.

Let’s look at an example that uses the Customers and Orders tables to create the CustomerOrderView view. See the Customers and Orders tables below:

Let’s create a CustomerOrderView view on top of the tables defined above:

CREATE VIEW CustomerOrderView AS
SELECT Id, CustName
FROM Customers
WHERE OrderId IN (
	SELECT Id
	FROM Orders
	WHERE Quantity>5 
AND (Product='Apple' OR Product='Pear'));

Now, instead of calling the complex query stored in this view, you can use a simple SELECT statement as shown below:

Scenario II

The access control to the data stored in the database is another reason why you should use SQL views. Usually, the rule of least privilege is followed (i.e., a user is given as limited privileges as possible that satisfy his/her requirements.) For example, if a user needs to read from table A, only the read privilege should be assigned to him/her and not the read/write privilege.

Let’s assume that we have a Clients table that contains sensitive data about the company’s clients. The administrative employees of this company should be able to see the clients’ names and email addresses but nothing else, and they should not have any direct access to the Clients table to read from or write to it.

How do we resolve this? Views! We can create an SQL view and allow all the administrative employees to read from this view.

See the Clients table below:

The query for creating a view that exposes the Id, FirstName, LastName, and Email columns of the Clients table is shown below:

CREATE VIEW ClientsView AS
SELECT Id, FirstName, LastName, Email
FROM Clients;

And now, we can assign permission to read from this view to all the administrative employees.

SELECT * FROM ClientsView;

Scenario III

SQL views are also helpful when refactoring a database. To understand the role of SQL views during the database refactoring process, let’s look at the examples below.

Before refactoring, the View1 view is built on top of the Table1 and Table2 tables, and the View2 view is built on top of the Table2 and Table3 tables. The user operates only on View1 and View2.

The database administrator decides to refactor the database so that the Table2 table contains the data of both the Table2 and Table3 tables. After such a refactoring process, the user still uses View1 and View2 as shown below, although the query of View2 was modified while refactoring. Hence, the user is not interrupted by the refactoring process.

If the user operates directly on the Table1 table, and the database administrator decides to refactor this table, a view could be introduced in place of this table. The user would not know the difference because views are like normal tables.

The decision about whether to use tables directly or views in place of tables is crucial at the beginning of creating the database structure. Check out our course track on Creating Database Structure to learn more!

Let’s look at a real-world example of refactoring database tables. Imagine that we have a Cars table that does not conform to the database normal forms but is nevertheless used by the database users. See the Cars table below:

We could introduce a CarView view in place of the Cars table. We would then be able to refactor the table to make it conform to the database normal forms.

CREATE VIEW CarView AS
SELECT *
FROM Cars;

Now, the CarView view can be used by the user instead of the Cars table, and the Cars table can undergo the refactoring process. Because views are used just like normal tables, the users will not experience any side effects of using a view in place of a table.

Conclusion

To summarize our journey through SQL views, they are relatively easy to use and can benefit the database in various ways. We can use them to save time and effort and to avoid any side effects of database administrative tasks.

It is essential to know that an SQL view is not a concrete table with data: it is just a saved SQL query. However, when used in SQL queries, it behaves exactly like a normal table. Hence, it is considered to be a powerful feature of SQL that should be understood by anyone interested in database design.