Lesson 18. Using Views
In this lesson, you’ll learn exactly what views are, how they work, and when they should be used. You’ll also see how views can be used to simplify some of the SQL operations performed in earlier lessons.
Understanding Views
Views are virtual tables. Unlike tables that contain data, views simply contain queries that dynamically retrieve data when used.
Note: Views in SQLite
SQLite supports only read-only views, so views may be created and read, but their contents cannot be updated.
The best way to understand views is to look at an example. Back in Lesson 12, “Joining Tables,” you used the following SELECT statement to retrieve data from three tables:
Input ▾
SELECT cust_name, cust_contact FROM Customers, Orders, OrderItems WHERE Customers.cust_id = Orders.cust_id AND OrderItems.order_num = Orders.order_num AND prod_id = 'RGAN01';
That query was used to retrieve the customers who had ordered a specific product. Anyone needing this data would have to understand the table structure, as well as how to create the query and join the tables. To retrieve the same data for another product (or for multiple products), you would have to modify the last WHERE clause.
Now imagine that you could wrap that entire query in a virtual table called ProductCustomers. You could then simply do the following to retrieve the same data:
Input ▾
SELECT cust_name, cust_contact FROM ProductCustomers WHERE prod_id = 'RGAN01';
This is where views come into play. ProductCustomers is a view, and as a view, it does not contain any columns or data. Instead, it contains a query—the same query used above to join the tables properly.
Tip: DBMS Consistency
You’ll be relieved to know that view creation syntax is supported pretty consistently by all the major DBMSs.
Why Use Views
You’ve already seen one use for views. Here are some other common uses:
To reuse SQL statements.
To simplify complex SQL operations. After the query is written, it can be reused easily, without having to know the details of the underlying query itself.
To expose parts of a table instead of complete tables.
To secure data. Users can be given access to specific subsets of tables instead of to entire tables.
To change data formatting and representation. Views can return data formatted and presented differently from their underlying tables.
For the most part, after views are created, they can be used in the same way as tables. You can perform SELECT operations, filter and sort data, join views to other views or tables, and possibly even add and update data. (There are some restrictions on this last item. More on that in a moment.)
The important thing to remember is views are just that—views into data stored elsewhere. Views contain no data themselves, so the data they return is retrieved from other tables. When data is added or changed in those tables, the views will return that changed data.
Caution: Performance Issues
Because views contain no data, any retrieval needed to execute a query must be processed every time the view is used. If you create complex views with multiple joins and filters, or if you nest views, you may find that performance is dramatically degraded. Be sure you test execution before deploying applications that use views extensively.
View Rules and Restrictions
Before you create views yourself, you should be aware of some restrictions. Unfortunately, the restrictions tend to be very DBMS specific, so check your own DBMS documentation before proceeding.
Here are some of the most common rules and restrictions governing view creation and usage:
Like tables, views must be uniquely named. (They cannot be named with the name of any other table or view.)
There is no limit to the number of views that can be created.
To create views, you must have security access. This level of access is usually granted by the database administrator.
Views can be nested; that is, a view may be built using a query that retrieves data from another view. The exact number of nested levels allowed varies from DBMS to DBMS. (Nesting views may seriously degrade query performance, so test this thoroughly before using it in production environments.)
Many DBMSs prohibit the use of the ORDER BY clause in view queries.
Some DBMSs require that every column returned be named; this will require the use of aliases if columns are calculated fields. (See Lesson 7, “Creating Calculated Fields,” for more information on column aliases.)
Views cannot be indexed, nor can they have triggers or default values associated with them.
Some DBMSs, like SQLite, treat views as read-only queries, meaning you can retrieve data from views but not write data back to the underlying tables. Refer to your DBMS documentation for details.
Some DBMSs allow you to create views that do not allow rows to be inserted or updated if that insertion or update will cause that row to no longer be part of the view. For example, if you have a view that retrieves only customers with email addresses, updating a customer to remove his email address would make that customer fall out of the view. This is the default behavior and is allowed, but depending on your DBMS, you might be able to prevent this from occurring.
Tip: Refer to Your DBMS Documentation
That’s a long list of rules, and your own DBMS documentation will likely contain additional rules too. It is worth taking the time to understand what restrictions you must adhere to before creating views.
Creating Views
So now that you know what views are (and the rules and restrictions that govern them), let’s look at view creation.
Views are created using the CREATE VIEW statement. Like CREATE TABLE, CREATE VIEW can only be used to create a view that does not exist.
Note: Renaming Views
To remove a view, you use the DROP statement. The syntax is simply DROP VIEW viewname;.
To overwrite (or update) a view, you must first DROP it and then re-create it.
Using Views to Simplify Complex Joins
One of the most common uses of views is to hide complex SQL, and this often involves joins. Look at the following statement:
Input ▾
CREATE VIEW ProductCustomers AS SELECT cust_name, cust_contact, prod_id FROM Customers, Orders, OrderItems WHERE Customers.cust_id = Orders.cust_id AND OrderItems.order_num = Orders.order_num;
Analysis ▾
This statement creates a view named ProductCustomers, which joins three tables to return a list of all customers who have ordered any product. If you were to use SELECT * FROM ProductCustomers, you’d list every customer who ordered anything.
To retrieve a list of customers who ordered product RGAN01, you can do the following:
Input ▾
SELECT cust_name, cust_contact FROM ProductCustomers WHERE prod_id = 'RGAN01';
Output ▾
cust_name cust_contact ------------------- ------------------ Fun4All Denise L. Stephens The Toy Store Kim Howard
Analysis ▾
This statement retrieves specific data from the view by issuing a WHERE clause. When the DBMS processes the request, it adds the specified WHERE clause to any existing WHERE clauses in the view query so that the data is filtered correctly.
As you can see, views can greatly simplify the use of complex SQL statements. Using views, you can write the underlying SQL once and then reuse it as needed.
Tip: Creating Reusable Views
It is a good idea to create views that are not tied to specific data. For example, the view created above returns customers for all products, not just product RGAN01 (for which the view was first created). Expanding the scope of the view enables it to be reused, making it even more useful. It also eliminates the need for you to create and maintain multiple similar views.
Using Views to Reformat Retrieved Data
As mentioned above, another common use of views is for reformatting retrieved data. The following SQL Server SELECT statement (from Lesson 7) returns vendor name and location in a single combined calculated column:
Input ▾
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title FROM Vendors ORDER BY vend_name;
Output ▾
vend_title ----------------------------------------------------------- Bear Emporium (USA) Bears R Us (USA) Doll House Inc. (USA) Fun and Games (England) Furball Inc. (USA) Jouets et ours (France)
The following is the same statement, but using the || syntax (as explained back in Lesson 7):
Input ▾
SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')' AS vend_title FROM Vendors ORDER BY vend_name;
Output ▾
vend_title ----------------------------------------------------------- Bear Emporium (USA) Bears R Us (USA) Doll House Inc. (USA) Fun and Games (England) Furball Inc. (USA) Jouets et ours (France)
Now suppose that you regularly needed results in this format. Rather than perform the concatenation each time it was needed, you could create a view and use that instead. To turn this statement into a view, you can do the following:
Input ▾
CREATE VIEW VendorLocations AS SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title FROM Vendors;
Here’s the same statement using || syntax:
Input ▾
CREATE VIEW VendorLocations AS SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')' AS vend_title FROM Vendors;
Analysis ▾
This statement creates a view using the exact same query as the previous SELECT statement. To retrieve the data to create all mailing labels, simply do the following:
Input ▾
SELECT * FROM VendorLocations;
Output ▾
vend_title ----------------------------------------------------------- Bear Emporium (USA) Bears R Us (USA) Doll House Inc. (USA) Fun and Games (England) Furball Inc. (USA) Jouets et ours (France)
Note: SELECT Restrictions All Apply
Earlier in this lesson I stated that the syntax used to create views is rather consistent between DBMSs. So why multiple versions of statements? A view simply wraps a SELECT statement, and the syntax of that SELECT must adhere to all the rules and restrictions of the DBMS being used.
Using Views to Filter Unwanted Data
Views are also useful for applying common WHERE clauses. For example, you might want to define a CustomerEMailList view so that it filters out customers without email addresses. To do this, you can use the following statement:
Input ▾
CREATE VIEW CustomerEMailList AS SELECT cust_id, cust_name, cust_email FROM Customers WHERE cust_email IS NOT NULL;
Analysis ▾
Obviously, when sending email to a mailing list, you’d want to ignore users who have no email address. The WHERE clause here filters out those rows that have NULL values in the cust_email columns so that they’ll not be retrieved.
View CustomerEMailList can now be used like any table:
Input ▾
SELECT * FROM CustomerEMailList;
Output ▾
cust_id cust_name cust_email ---------- ------------ --------------------- 1000000001 Village Toys sales@villagetoys.com 1000000003 Fun4All jjones@fun4all.com 1000000004 Fun4All dstephens@fun4all.com
Note: WHERE Clauses and WHERE Clauses
If a WHERE clause is used when retrieving data from the view, the two sets of clauses (the one in the view and the one passed to it) will be combined automatically.
Using Views with Calculated Fields
Views are exceptionally useful for simplifying the use of calculated fields. The following SELECT statement was introduced in Lesson 7. It retrieves the order items for a specific order, calculating the expanded price for each item:
Input ▾
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;
Output ▾
prod_id quantity item_price expanded_price -------- --------- ----------- -------------- RGAN01 5 4.9900 24.9500 BR03 5 11.9900 59.9500 BNBG01 10 3.4900 34.9000 BNBG02 10 3.4900 34.9000 BNBG03 10 3.4900 34.9000
To turn this into a view, do the following:
Input ▾
CREATE VIEW OrderItemsExpanded AS SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems
To retrieve the details for order 20008 (the output above), do the following:
Input ▾
SELECT * FROM OrderItemsExpanded WHERE order_num = 20008;
Output ▾
order_num prod_id quantity item_price expanded_price --------- ------- --------- ---------- -------------- 20008 RGAN01 5 4.99 24.95 20008 BR03 5 11.99 59.95 20008 BNBG01 10 3.49 34.90 20008 BNBG02 10 3.49 34.90 20008 BNBG03 10 3.49 34.90
As you can see, views are easy to create and even easier to use. Used correctly, views can greatly simplify complex data manipulation.
Summary
Views are virtual tables. They do not contain data, but instead, they contain queries that retrieve data as needed. Views provide a level of encapsulation around SQL SELECT statements and can be used to simplify data manipulation, as well as to reformat or secure underlying data.
Challenges
Create a view called
CustomersWithOrdersthat contains all of the columns inCustomersbut includes only those who have placed orders. Hint: you can useJOINon theOrderstable to filter just the customers you want. Then use aSELECTto make sure you have the right data.What is wrong with the following SQL statement? (Try to figure it out without running it.)
CREATE VIEW OrderItemsExpanded AS SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems ORDER BY order_num;