websql

22 Using Views

Learning Objectives

:

Describe what a view is and how it differs from a table.

Describe common uses and restrictions of views.

Use CREATE VIEW to simplify a complex join.

Use a view to reformat retrieved data or filter out unwanted rows.

Use a view to simplify a query that includes a calculated field.

22.1 Understanding Views

Views are virtual tables. Unlike tables that contain data, views simply contain queries that dynamically retrieve data when used.

Updatable Views in PostgreSQL. Unlike some DBMSs, which treat every view as strictly read-only, PostgreSQL automatically allows you to INSERT, UPDATE, or DELETE through a "simple" view — one built from a single table, without joins, aggregates, DISTINCT, or GROUP BY. More complex views, like the ones built in this lesson, are read-only by default, though PostgreSQL lets you make them writable too by defining an INSTEAD OF trigger.

The best way to understand views is to look at an example. Back in the lesson on joining tables, you used a SELECT statement like the following to retrieve data from three tables:

SELECT full_name, nationality
FROM artist, work, subject
WHERE artist.artist_id = work.artist_id
AND subject.work_id = work.work_id
AND subject.subject = 'Portrait';

That query was used to retrieve the artists who had created a work with a specific subject. 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 subject (or for multiple subjects), you would have to modify the last WHERE clause.

Now imagine that you could wrap that entire query in a virtual table called ArtistsBySubject. You could then simply do the following to retrieve the same data:

SELECT full_name, nationality
FROM ArtistsBySubject
WHERE subject = 'Portrait';

This is where views come into play. ArtistsBySubject 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.

DBMS Consistency. You'll be relieved to know that view creation syntax is supported pretty consistently by all the major DBMSs.

22.2 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.

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.

22.3 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 the lesson on creating calculated fields for more information on column aliases.)
Views cannot be indexed, nor can they have triggers or default values associated with them.
In PostgreSQL, only "simple" views (built from a single table, with no joins, aggregates, DISTINCT, or GROUP BY) are automatically updatable. More complex views are read-only unless you define an INSTEAD OF trigger to handle writes yourself.
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 museums with a website on file, updating a museum to remove its website would make that museum 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.

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.

22.4 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.

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.

22.5 Using Views to Simplify Complex Joins

One of the most common uses of views is to hide complex SQL, and this often involves joins. You can use a chat with an AI assistant to create this view:

Write a SQL statement to create a view named ArtistsBySubject that joins the artist, work, and subject tables and returns full_name, nationality, and the subject.send

A view wraps a SELECT statement with a CREATE VIEW header. Here's the SQL statement:
CREATE VIEW ArtistsBySubject AS
SELECT full_name, nationality, subject.subject
FROM artist, work, subject
WHERE artist.artist_id = work.artist_id
AND subject.work_id = work.work_id;
This creates a view named ArtistsBySubject that joins the three tables to return the full_name, nationality, and subject for every recorded work, without restricting the results to any single subject. You can filter the view by subject afterward with a plain WHERE clause.

Try the SQL statement.

CREATE VIEW ArtistsBySubject AS
SELECT full_name, nationality, subject.subject
FROM artist, work, subject
WHERE artist.artist_id = work.artist_id
AND subject.work_id = work.work_id;

This statement creates a view named ArtistsBySubject, which joins three tables to return a list of every artist along with the subject of each work they created. If you were to use SELECT * FROM ArtistsBySubject, you'd list every artist and every subject recorded.

To retrieve a list of artists who created a work with the subject "Portrait," you can do the following:

SELECT full_name, nationality
FROM ArtistsBySubject
WHERE subject = 'Portrait';

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.

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 artists for every subject, not just the subject "Portrait" (for which the view was first demonstrated). 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.

22.6 Using Views to Reformat Retrieved Data

As mentioned above, another common use of views is for reformatting retrieved data. The following SELECT statement, using the same concatenation pattern shown earlier in this book, returns the museum name and city in a single combined calculated column:

SELECT RTRIM(name) || ' (' || RTRIM(city) || ')' AS museum_title
FROM museum
ORDER BY name;

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:

CREATE VIEW MuseumLocations AS
SELECT RTRIM(name) || ' (' || RTRIM(city) || ')' AS museum_title
FROM museum;

This statement creates a view using the exact same query as the previous SELECT statement. To retrieve the data to create a full list of museum labels, simply do the following:

SELECT * FROM MuseumLocations;

SELECT Restrictions All Apply. Earlier in this lesson it was stated that the syntax used to create views is rather consistent between DBMSs. 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 — including how it handles string concatenation.

22.7 Using Views to Filter Unwanted Data

Views are also useful for applying common WHERE clauses. For example, you might want to define a MuseumsWithWebsite view so that it filters out museums without a website on file. To do this, you can use the following statement:

CREATE VIEW MuseumsWithWebsite AS
SELECT museum_id, name, url
FROM museum
WHERE url IS NOT NULL;

Obviously, when publishing a directory of museum websites, you'd want to ignore museums that have no website listed. The WHERE clause here filters out those rows that have NULL values in the url column so that they'll not be retrieved.

View MuseumsWithWebsite can now be used like any table:

SELECT * FROM MuseumsWithWebsite ORDER BY name;

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.

22.8 Using Views with Calculated Fields

Views are exceptionally useful for simplifying the use of calculated fields. This calculation was introduced in the lesson on summarizing data. The following SELECT statement retrieves the works for a specific artist, calculating the area for each work:

SELECT work_id,
       name,
       width, height,
       width * height AS area
FROM work
WHERE artist_id = 500;

To turn this into a view, you can use a chat with an AI assistant to write the SELECT statement:

Write a SQL statement to create a view named WorkAreas that lists work_id, artist_id, name, width, height, and a calculated area (width times height) for every work.send

Here's the SQL statement:
CREATE VIEW WorkAreas AS
SELECT work_id, artist_id, name, width, height, width * height AS area
FROM work;
This view calculates the area column for every work in the work table, without restricting the results to any single artist. You can filter the view by artist_id afterward.

Try the SQL statement.

CREATE VIEW WorkAreas AS
SELECT work_id, artist_id, name, width, height, width * height AS area
FROM work;

To retrieve the details for the works created by artist 500 (Pierre-Auguste Renoir, the output above), do the following:

SELECT *
FROM WorkAreas
WHERE artist_id = 500;

As you can see, views are easy to create and even easier to use. Used correctly, views can greatly simplify complex data manipulation.

22.9 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.