Back to articles list December 11, 2020 - 10 minutes read What is an SQL View? Martyna Sławińska Martyna is a software developer with a passion for programming, automation, and innovation. Currently working for the global corporate IT consulting sector, she is a new-generation techie who hails from Warsaw University of Technology, Poland. She has certifications in both database administration and Java development. Her interests include creating small automation devices using embedded systems and various electronic components. In her free time, she practices yoga, which helps her to center her thoughts and come up with new ideas. Tags: sql learn sql Data Engineering 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: IdNameFriendId ……… 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: IdFoodName 1Meat 2Algae 3Seeds AnimalIdAnimalTypeFoodId 1Lion1 2Fish2 3Bird3 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: AnimalTypeFoodName LionMeat FishAlgae BirdSeeds 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: AnimalTypeFoodName FishAlgae BirdSeeds 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; AnimalIdFoodName 1Meat 2Algae 3Seeds 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: IdCustNameOrderId 1Anne1 2Carl2 3Taylor3 IdProductQuantity 1Apple4 2Apple7 3Pear9 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: IdFirstNameLastNameEmailPhoneNoSSNDateOfBirth 1BenReynoldsben.firstname.lastname@example.org 2BettyConneybetty.email@example.com 3AliceKenfordalice.firstname.lastname@example.org 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; IdFirstNameLastNameEmail 1BenReynoldsben.email@example.com 2BettyConneybetty.firstname.lastname@example.org 3AliceKenfordalice.email@example.com 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: IdCarNameModelProductionYears 1Ford Kuga2010, 2014, 2019 2Mercedes Benz2013, 2017 3Toyota Yaris2007, 2009 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. Tags: sql learn sql Data Engineering You may also like What Is Data Engineering? What’s data engineering and how does it fit in with data science and Big Data? Read this article to find out. Read more Who Is a Data Engineer? Do you know what Data Engineers do? Do you know what skills they need? Read our article to find out more about this relatively new IT position. Read more What Is a DBMS? Read our article to find out what a DBMS is and what it can do for you and your data. Read more Is SQL Worth Learning? Is learning SQL something to consider? Or is it yet another useless skill that employers don’t value? Read more How to Create Your First Table in SQL Use the CREATE TABLE SQL statement to create your first database table. Read more Career Change: From Accountant to Database Designer Personal insight into career change, learning SQL, becoming a database designer, and how your life choices can help you accomplish all that and more. Read more The Most Important SQL Queries for Beginners This guide discusses the 30 most important SQL queries for beginners. Let's take a look. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.