How To Use Views in SQL
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 views in SQL are and why they can be useful. It also highlights how you can create, query, modify, and destroy views in SQL using standard SQL syntax.
Prerequisites for Using Views in SQL
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 in SQL. 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 in SQL
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 in SQL 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_name
AS
SELECT 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. This query tries to retrieve the walker column from walking_schedule, but it fails as the view lacks that column:
SELECT walker FROM walking_schedule;
Output
ERROR 1054 (42S22): Unknown column 'walker' in 'field list'
The error occurs because the walker column is in the dogs table but not included in the view.
You can also run queries that include aggregate functions that manipulate the data within a view. This example uses MAX and GROUP BY to find each employee’s longest walking distance for a 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)
Views are useful for limiting a user’s access to specific data within a view, not an entire table.
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';
You can grant this user read access to only the walking_schedule view with a GRANT statement like this:
GRANT SELECT ON views_db.walking_schedule to 'office_mgr'@'localhost';
After this, anyone using the office_mgr account can only run SELECT queries on the walking_schedule view.
Changing and Deleting Views in SQL
If you change data in a base table, the view automatically reflects the updated information. 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.
The view still pulls data from the same base tables, so this operation didn’t alter the view itself.
Many RDBMSs let you update a view’s structure using the CREATE OR REPLACE VIEW syntax:
Example CREATE OR REPLACE VIEW syntax
CREATE OR REPLACE VIEW view_name
AS
new SELECT statement
If a view named view_name exists, this syntax updates it to reflect the new SELECT statement’s data. If a view by that name doesn’t already exist, then the DBMS will create a new one.
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, but its data remains unless deleted from the base tables.
Conclusion
This guide teaches what views in SQL are and how to create, query, modify, and delete them. You learned why views are useful and created a MySQL user with read-only access to a sample view.
The example commands should work on most databases, but each SQL database has its own unique implementation. Consult your DBMS’s documentation for detailed command descriptions and full option sets.
To learn more about SQL, check out other tutorials in our How To Use SQL series.
