Structured Query Language/Views
Often users and applications request information in a form which differs from the structure of existing tables. To achieve those requests the SELECT command offers plenty possibilities: projections, joins, group by clause and so on. If there are always the same requests, what is the case in particular for applications, or if the table structure intentionally should be hidden from the application-level, views can be defined. Furthermore the access rights to views may be different from those to tables.
Views look like a table. They have columns of a certain data type, which can be retrieved in the same way as columns of a table. But views are only definitions, they don't have data of its own! Their data is always the data of a table or is based on another view. A view is a different sight to the stored data or somewhat like a predefined SELECT.
Create a View
One creates a view by specify its name, column names - which is optionally - and especially the SELECT command on which the view is based. Within this SELECT all elements are allowed in the same way as in a standalone SELECT command. If no column names are specified the column names of the SELECT are used.
CREATE VIEW <view_name> [(column_name, ...)] AS SELECT ... -- as usual ;
Examples and Explanations
Example 1: Hid Columns
As a first example here is the view person_view_1 which contains all but id and ssn columns of table person. Users which have the right to read from this view but not from the table person doesn't have access to id and ssn.
CREATE VIEW person_view_1 AS SELECT firstname, lastname, date_of_birth, place_of_birth, weight FROM person; -- SELECTs on views have identical syntax as SELECTs on tables SELECT * FROM person_view_1 ORDER BY lastname; -- The column 'id' is not part of the view. Therefore it is not seen and cannot be used -- anywhere in SELECTs to person_view_1. -- This SELECT will generate an error message because of missing 'id' column: SELECT * FROM person_view_1 WHERE id = 5;
As indicated in the above 'order by' example it is possible to use all columns of the view (but not all of the table!) within any part of SELECTs to the view: in the projection, the WHERE, ORDER BY, GROUP BY and HAVING clauses, in function calls and so on.
-- SELECTs on views have identical syntax as SELECTs on tables SELECT COUNT(lastname), lastname FROM person_view_1 GROUP BY lastname ORDER BY lastname;
Example 2: Rename Columns
Next there is a renaming of a column. The column name lastname of the table will be familyname in the view.
-- first technique: list the desired column names within parenthesis after the view name CREATE VIEW person_view_2a (firstname, familyname, date_of_birth, place_of_birth, weight) AS SELECT firstname, lastname, date_of_birth, place_of_birth, weight FROM person; -- second technique: rename the column in the SELECT part CREATE VIEW person_view_2b AS SELECT firstname, lastname AS familyname, date_of_birth, place_of_birth, weight FROM person; -- Hint: technique 1 overwrites technique 2 -- Access to person.lastname is possible via person_view_2a.familyname or person_view_2b.familyname. -- The objects person.familyname, person_view_2a.lastname or person_view_2b.lastname does not exist!
Example 3: Apply WHERE Condition
Not only columns can be hidden in a view. It's also possible to hid complete rows, because the view definition may contain a WHERE clause.
-- Restrict access to few rows CREATE VIEW person_view_3 AS SELECT firstname, lastname, date_of_birth, place_of_birth, weight FROM person WHERE place_of_birth IN ('San Francisco', 'Richland'); -- Verify result: SELECT * FROM person_view_3;
This view contains only persons born in San Francisco or Richland. All other persons are hidden. Therefore the following SELECT retrieves nothing although there are persons in the table which fulfil the condition.
-- No hit SELECT * FROM person_view_3 WHERE place_of_birth = 'Dallas'; -- One hit SELECT * FROM person WHERE place_of_birth = 'Dallas';
Example 4: Use Functions
This example usues the sum() function.
-- CREATE VIEW person_view_4 AS -- General hint: Please consider that not all columns are availabe in a SELECT containing a GROUP BY clause SELECT lastname, COUNT(lastname) AS count_of_members FROM person GROUP BY lastname HAVING COUNT(*) > 1; -- Verify result: 2 rows SELECT * FROM person_view_4; -- The computed column 'count_of_members' may be part of a WHERE condition. -- This SELECT results in 1 row SELECT * FROM person_view_4 WHERE count_of_members > 2;
In this example the elaborated construct 'GROUP BY / HAVING' is hidden from users and applications.
Example 5: Join
Next, there is an example where a view contains columns out of serveral tables. To do so a JOIN is neccessary. The view contains the name of persons in combination with the available contact information. As an INNER JOIN is used, some persons occur multiple, others not at all.
-- Persons and contacts CREATE VIEW person_view_5 AS SELECT p.firstname, p.lastname, c.contact_type, c.contact_value FROM person p JOIN contact c ON p.id = c.person_id; -- Verify result SELECT * FROM person_view_5; SELECT * FROM person_view_5 WHERE lastname = 'Goldstein';
The columns person.id an contact.person_id are used during the definition of the view. But they are not part of the projection and hence not available for SELECTs to the view.
Hint: The syntax and semantic of join operations is explained on a separate page.
Some more Hints
Within a CREATE VIEW statement one may use more elements of the regular SELECT statement than it is shown on this page, especially: SET operations, recursive definitions, CASE expressions, ORDER BY and so on.
If there is an ORDER BY clause within the CREATE VIEW and another one in a SELECT to this view, the later one overwrites the former.
Write Access via Views
In some cases, but not in general, it should be possible to change data (UPDATE, INSERT or DELETE command) in a table by accessing it via a view. Assume, as an counterexample, that one wants to change the column count_of_members of person_view_4 to a different value. What shall the DBMS do? The column is subject to an aggregate function which counts the number of existing rows in the underlying table. Shall it add some more random values into new rows respectively shall it delete random rows to satisfy the new value of count_of_members? Of course not!
On the other hand a very simple view like 'CREATE VIEW person_0 AS SELECT * from person;', which is an 1:1 copy of the original table, should be manageable by the DBMS. Where is the borderline between updateable and non updateable views? The SQL standard does not define it. But the concrete SQL implementations offer limited write-access to views based on their own rules. These rules sometimes are very fix, in other cases they consists of flexible techniques like triggers to give programmers the chance to implement their own rules.
Here are some general rules which may be part of the implementors fixed rules to define, which views are updateable in his sens:
- The view definition is based on one and only one table. It includes the Primary Key of this underlying table.
- The view definition must not use any aggregate function.
- The view definition must not have any DISTINCT-, GROUP BY- or HAVING-clause.
- The view definition must not have any JOIN, SUBQUERY, SET operation, EXISTS or NOT EXISTS predicate.
If it is possible to use the UPDATE, INSERT or DELETE command to a view, the syntax is the same as with tables.
Clean up the Example Database
The DROP VIEW statement deletes a view definition. In doing so the data of the underlying table(s) is not affected.
Don't confuse the DROP command (definitions) with the DELETE command (data)!
DROP VIEW person_view_1; DROP VIEW person_view_2a; DROP VIEW person_view_2b; DROP VIEW person_view_3; DROP VIEW person_view_4; DROP VIEW person_view_5;
Create a view 'hobby_view_1' which contains all columns of table 'hobby' except 'id'.
Rename column 'remark' to 'explanation'. Create two different solutions.
CREATE VIEW hobby_view_1a AS SELECT hobbyname, remark AS explanation FROM hobby; -- Verification SELECT * FROM hobby_view_1a; CREATE VIEW hobby_view_1b (hobbyname, explanation) AS SELECT hobbyname, remark FROM hobby; -- Verification SELECT * FROM hobby_view_1b;
Create a view 'hobby_view_2' with the same criteria as in the previous example. The only difference
is that the length of the explanation column is limited to 30 character. Hint: use the function
substr(<column name>, 1, 30) to determine the first 30 characters - she is not part of the SQL standard but works in plenty implementation.
CREATE VIEW hobby_view_2 AS SELECT hobbyname, substr(remark, 1, 30) AS explanation FROM hobby; -- Verification SELECT * FROM hobby_view_2;
Create a view 'contact_view_3' which contains all rows of table contact with the exception of the 'icq' rows. Count the number of the view rows and compare it with the number of rows in the table 'contact'.
CREATE VIEW contact_view_3 AS SELECT * FROM contact WHERE contact_type != 'icq'; -- an alternate operator with the same semantic as '!=' is '<>' -- Verification SELECT 'view', COUNT(*) FROM contact_view_3 UNION SELECT 'table', COUNT(*) FROM contact;
Create a view 'contact_view_4' which contains one row per contact type with its notation and the number of occurences. Afterwords select those which occur more than once.
CREATE VIEW contact_view_4 AS SELECT contact_type, COUNT(*) AS cnt FROM contact GROUP BY contact_type; -- Verification SELECT * FROM contact_view_4; -- Use columns of a view with the same syntax as column of a table. SELECT * FROM contact_view_4 WHERE cnt > 2;
Create a view 'person_view_6' which contains first- and lastname of persons plus the number of persons with the same name as the person itself (family name). Hint: the solution uses a correlated subquery.
CREATE VIEW person_view_6 AS SELECT firstname, lastname, (SELECT COUNT(*) FROM person sq WHERE sq.lastname = p.lastname) AS cnt_family FROM person p; -- Verification SELECT * FROM person_view_6;
Clean up the example database.
DROP VIEW hobby_view_1a; DROP VIEW hobby_view_1b; DROP VIEW hobby_view_2; DROP VIEW contact_view_3; DROP VIEW contact_view_4; DROP VIEW person_view_6;