How To Use Views in SQL | DigitalOcean (2023)

Introduction

Structured Query Language (SQL) employs a variety of different data structures, with tables being one of the most commonly used. However, tables have certain limitations. For instance, you can’t limit users to only have access to part of a table. A user must be granted access to an entire table, not just a few columns within it.

As another example, say you want to combine data from multiple other tables into a new structure, but you also don’t want to delete the original tables. You could just create another table, but then you’d have redundant data stored in multiple places. This could cause a lot of inconvenience: if some of your data changed, you’d have to update it in multiple places. In cases like these, views can come in handy.

In SQL, a view is a virtual table whose contents are the result of a specific query to one or more tables, known as base tables. This guide provides an overview of what SQL views are and why they can be useful. It also highlights how you can create, query, modify, and destroy views using standard SQL syntax.

Prerequisites

In order to follow this guide, you will need a computer running some type of relational database management system (RDBMS) that uses SQL. The instructions and examples in this guide were validated using the following environment:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and a firewall configured with UFW, as described in our initial server setup guide for Ubuntu 20.04.
• MySQL installed and secured on the server, as outlined in How To Install MySQL on Ubuntu 20.04. This guide was verified with a newly-created user, as described in Step 3.

Note: Please note that many RDBMSs use their own unique implementations of SQL. Although the commands outlined in this tutorial will work on most RDBMSs, the exact syntax or output may differ if you test them on a system other than MySQL.

You’ll also need a database with some tables loaded with sample data which you can use to practice creating and working with views. We encourage you to go through the following Connecting to MySQL and Setting up a Sample Database section for details on how to connect to a MySQL server and create the testing database used in examples throughout this guide.

Connecting to MySQL and Setting up a Sample Database

If your SQL database system runs on a remote server, SSH into your server from your local machine:

ssh sammy@your_server_ip

Then open up the MySQL server prompt, replacing sammy with the name of your MySQL user account:

mysql -u sammy -p

From the prompt, create a database named views_db:

CREATE DATABASE views_db;

If the database was created successfully, you’ll receive output like the following:

Output
Query OK, 1 row affected (0.01 sec)

To select the views_db database, run the following USE statement:

USE views_db;

Output
Database changed

After selecting views_db, create a few tables within it.

To follow along with the examples used in this guide, imagine that you run an at-home dog care service. You decide to use an SQL database to store information about each dog you’ve signed up for the service, as well as each of the dog care professionals your service employs. To keep things organized, you decide you need two tables: one representing the employees and one representing the dogs cared for by your service. The table representing your employees will contain the following columns:

• emp_id: an identification number for each dog carer you employ, expressed with the int data type. This column will serve as the table’s primary key, meaning that each value will function as a unique identifier for its respective row. Because every value in a primary key must be unique, this column will also have a UNIQUE constraint applied to it.
• emp_name: each employee’s name expressed using the varchar data type with a maximum of 20 characters.

Run the following CREATE TABLE statement to create a table named employees that has these two columns:

CREATE TABLE employees (
emp_id int UNIQUE,
emp_name varchar(20),
PRIMARY KEY (emp_id)
);

The other table representing each dog will contain these six columns:

• dog_id: an identification number for each dog staying at the hotel, expressed with the int data type. Like the emp_id column in the employees table, this column will serve as the primary key for the dogs table.
• dog_name: each dog’s name, expressed using the varchar data type with a maximum of 20 characters.
• walker: this column stores the employee ID number of the employee assigned to care for each respective dog.
• walk_distance: the distance each dog should walk when they’re taken out for exercise, expressed using the decimal data type. In this case, the decimal declaration specifies a precision of three with a scale of two, meaning that any values in this column can have three digits at most, with two of those digits being to the right of the decimal point.
• meals_perday: the dog hotel provides each dog with a certain number of meals each day. This column holds the number of meals each dog should receive per day as requested by their owner, and it uses int, an integer.
• cups_permeal: this column lists how many cups of kibble each dog should receive per meal. Like the walk_distance column, this column is expressed as a decimal. However, this one has a scale of three with a precision of two, meaning values in this column can have up to three digits with two of those digits being to the right of the decimal point.

To ensure that the walker column only holds values that represent valid employee ID numbers, you decide to apply a foreign key constraint to the walker column that references the employees table’s emp_ID column. A foreign key constraint is a way to express a relationship between two tables by requiring that values in the column on which the foreign key has been applied must already exist in the column that it references. In the following example, the FOREIGN KEY constraint requires that any value added to the walker column in the dogs table must already exist in the employees table’s emp_ID column.

Create a table named dogs that has these columns with the following command:

CREATE TABLE dogs (
dog_id int UNIQUE,
dog_name varchar(20),
walker int,
walk_distance decimal(3,2),
meals_perday int,
cups_permeal decimal(3,2),
PRIMARY KEY (dog_id),
FOREIGN KEY (walker)
REFERENCES employees(emp_ID)
);

Now you can load both tables with some sample data. Run the following INSERT INTO operation to add three rows of data representing three of your service’s employees to the employees table:

INSERT INTO employees
VALUES
(1, 'Peter'),
(2, 'Paul'),
(3, 'Mary');

Then run the following operation to insert seven rows of data into the dogs table:

INSERT INTO dogs
VALUES
(1, 'Dottie', 1, 5, 3, 1),
(2, 'Bronx', 3, 6.5, 3, 1.25),
(3, 'Harlem', 3, 1.25, 2, 0.25),
(4, 'Link', 2, 2.75, 2, 0.75),
(5, 'Otto', 1, 4.5, 3, 2),
(6, 'Juno', 1, 4.5, 3, 2),
(7, 'Zephyr', 3, 3, 2, 1.5);

With that, you’re ready to follow the rest of the guide and begin learning how to use views in SQL.

Understanding and Creating Views

Depending on the scenario, SQL queries can become surprisingly complex. Indeed, one of the main benefits of SQL is that it includes many different options and clauses that allow you to filter your data with a high level of granularity and specificity. If you have complex queries that you need to run frequently, having to continually write them out can quickly become frustrating. One way to work around these issues is by using views.

As mentioned in the introduction, views are virtual tables. This means that although a view is functionally similar to a table, it is a different type of structure since the view doesn’t hold any data of its own. Instead, it pulls in data from one or more base tables that actually hold the data. The only information about a view that a DBMS will store is the view’s structure. Views are sometimes called saved queries, because that’s essentially what they are: queries that have been saved under a specific name for convenient access.

To better understand views, consider the following example scenario. Imagine that your dog care business is doing well and you need to print out a daily schedule for all your employees. The schedule should list each dog being cared for by the service, the employee who is assigned to care for them, the distance each dog should be walked every day, the number of meals each dog should be fed per day, and the amount of kibble each dog should get at every meal.

Using your SQL skills, you create a query with the sample data from the previous step to retrieve all of this information for the schedule. Note that this query includes the JOIN syntax in order to pull data from both the employees and dogs tables:

SELECT emp_name, dog_name, walk_distance, meals_perday, cups_permeal
FROM employees JOIN dogs ON emp_ID = walker;

Output
+----------+----------+---------------+--------------+--------------+| emp_name | dog_name | walk_distance | meals_perday | cups_permeal |+----------+----------+---------------+--------------+--------------+| Peter | Dottie | 5.00 | 3 | 1.00 || Peter | Otto | 4.50 | 3 | 2.00 || Peter | Juno | 4.50 | 3 | 2.00 || Paul | Link | 2.75 | 2 | 0.75 || Mary | Bronx | 6.50 | 3 | 1.25 || Mary | Harlem | 1.25 | 2 | 0.25 || Mary | Zephyr | 3.00 | 2 | 1.50 |+----------+----------+---------------+--------------+--------------+7 rows in set (0.00 sec)

Say you have to run this query on a regular basis. This could become tedious to have to write out the query repeatedly, especially when it comes to longer and more complex query statements Also, if you had to make slight tweaks to the query or expand on it, it could be frustrating when troubleshooting mistakes with so many possibilities for syntax errors.

A view could be useful in cases like this, since a view is essentially a table derived from the results of a query.

To create a view, most RDBMSs use the following syntax:

Example CREATE VIEW syntax

CREATE VIEW view_nameASSELECT statement;

After the CREATE VIEW statement, you define a name for the view that you’ll use to refer to it later on. After the name, you enter the AS keyword and then the SELECT query whose output you want to save. The query you use to create your view can be any valid SELECT statement. The statement you include can query one or more base tables as long as you use the correct syntax.

Try creating a view using the previous example query. This CREATE VIEW operation names the view walking_schedule:

CREATE VIEW walking_schedule
AS
SELECT emp_name, dog_name, walk_distance, meals_perday, cups_permeal 
FROM employees JOIN dogs
ON emp_ID = walker;

Following that, you’ll be able to use and interact with this view like you would any other table.For instance, you could run the following query to return all the data held within the view:

SELECT * FROM walking_schedule;

Output
+----------+----------+---------------+--------------+--------------+| emp_name | dog_name | walk_distance | meals_perday | cups_permeal |+----------+----------+---------------+--------------+--------------+| Peter | Dottie | 5.00 | 3 | 1.00 || Peter | Otto | 4.50 | 3 | 2.00 || Peter | Juno | 4.50 | 3 | 2.00 || Paul | Link | 2.75 | 2 | 0.75 || Mary | Bronx | 6.50 | 3 | 1.25 || Mary | Harlem | 1.25 | 2 | 0.25 || Mary | Zephyr | 3.00 | 2 | 1.50 |+----------+----------+---------------+--------------+--------------+7 rows in set (0.00 sec)

Although this view is derived from two other tables, you won’t be able to query the view for any data from those tables unless it already exists in the view. The following query attempts to retrieve the walker column from the walking_schedule view, but this query will fail since the view doesn’t contain any columns with that name:

SELECT walker FROM walking_schedule;

Output
ERROR 1054 (42S22): Unknown column 'walker' in 'field list'

This output returns an error message because the walker column is part of the dogs table, but was not included in the view you created.

You can also run queries that include aggregate functions that manipulate the data within a view. The following example uses the MAX aggregate function along with GROUP BY to find the longest distance each employee will have to walk on a given day:

SELECT emp_name, MAX(walk_distance) AS longest_walks
FROM walking_schedule GROUP BY emp_name;

Output
+----------+---------------+| emp_name | longest_walks |+----------+---------------+| Peter | 5.00 || Paul | 2.75 || Mary | 6.50 |+----------+---------------+3 rows in set (0.00 sec)

As mentioned previously, another reason why views are useful is that you can use them to limit a database user’s access to only a view, rather than an entire table or database.

For example, say you hire an office manager to help you manage the schedule. You want them to have access to the schedule information, but not any other data in the database. To do this, you could create a new user account for them in your database:

CREATE USER 'office_mgr'@'localhost' IDENTIFIED BY 'password';

Then, you could then grant this new user read access to the walking_schedule view, and only that view, with a GRANT statement like the following:

GRANT SELECT ON views_db.walking_schedule to 'office_mgr'@'localhost';

Following this, anyone with access to the office_mgr MySQL user account would only be able to run SELECT queries on the walking_schedule view.

Changing and Deleting Views

If you add or change any of the data in one of the tables from which the view is derived, the relevant data will automatically be added or updated in the view. Run the following INSERT INTO command to add another row to the dogs table:

INSERT INTO dogs VALUES (8, 'Charlie', 2, 3.5, 3, 1);

Then retrieve all the data from the walking_schedule view again:

SELECT * FROM walking_schedule;

Output
+----------+----------+---------------+--------------+--------------+| emp_name | dog_name | walk_distance | meals_perday | cups_permeal |+----------+----------+---------------+--------------+--------------+| Peter | Dottie | 5.00 | 3 | 1.00 || Peter | Otto | 4.50 | 3 | 2.00 || Peter | Juno | 4.50 | 3 | 2.00 || Paul | Link | 2.75 | 2 | 0.75 || Paul | Charlie | 3.50 | 3 | 1.00 || Mary | Bronx | 6.50 | 3 | 1.25 || Mary | Harlem | 1.25 | 2 | 0.25 || Mary | Zephyr | 3.00 | 2 | 1.50 |+----------+----------+---------------+--------------+--------------+8 rows in set (0.00 sec)

This time, there’s another row in the query’s result set reflecting the data you added to the dogs table.

However, the view is still pulling the same data from the same base tables, so this operation did not change the view itself.

Many RDBMSs allow you to update the structure of the view after you create it by using the CREATE OR REPLACE VIEW syntax:

Example CREATE OR REPLACE VIEW syntax

CREATE OR REPLACE VIEW view_nameASnew SELECT statement

With this syntax, if a view with the name view_name already exists, the database system will update it so that it represents the data returned by the new SELECT statement. If a view by that name doesn’t already exist, then the DBMS will create a new one.

Say you wanted to change the walking_schedule view so that instead of listing how many cups of food per meal each dog eats, it listed how much total food each dog ate throughout the day. You can change the view with the following command:

CREATE OR REPLACE VIEW walking_schedule
AS
SELECT emp_name, dog_name, walk_distance, meals_perday, (cups_permeal * meals_perday) AS total_kibble 
FROM employees JOIN dogs ON emp_ID = walker;

Now when you query this view, the result set will reflect the view’s new data:

SELECT * FROM walking_schedule;

Output
+----------+----------+---------------+--------------+--------------+| emp_name | dog_name | walk_distance | meals_perday | total_kibble |+----------+----------+---------------+--------------+--------------+| Peter | Dottie | 5.00 | 3 | 3.00 || Peter | Otto | 4.50 | 3 | 6.00 || Peter | Juno | 4.50 | 3 | 6.00 || Paul | Link | 2.75 | 2 | 1.50 || Paul | Charlie | 3.50 | 3 | 3.00 || Mary | Bronx | 6.50 | 3 | 3.75 || Mary | Harlem | 1.25 | 2 | 0.50 || Mary | Zephyr | 3.00 | 2 | 3.00 |+----------+----------+---------------+--------------+--------------+8 rows in set (0.00 sec)

Like most other structures one can create in SQL, you can delete views using the DROP syntax:

Example DROP VIEW syntax

DROP VIEW view_name;

For instance, if you ever wanted to drop the walking_schedule view, you’d do so with the following command:

DROP VIEW walking_schedule;

This removes the walking_schedule view from your database, but none of the view’s data will be deleted unless you remove it from the base tables.

Conclusion

By reading this guide, you learned what SQL views are, how to create, query, change, and delete them from a database. You also learned why views can be useful, and created a MySQL user who could only read data from the sample view created in this guide.

While the commands from our examples should work on most relational databases, be aware that every SQL database uses its own unique implementation of the language. You should consult your DBMS’s official documentation for a more complete description of each command and their full sets of options.

If you’d like to learn more about working with SQL, we encourage you to check out the other tutorials in this series on How To Use SQL.