Views

SELECT: Subquery

Structured Query Language Views

INSERT 1

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 of possibilities: projections, joins, group by clause, and so on. If there are always the same requests, which is the case particularly 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.

One creates a view by specifying its name, optionally column names, 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
;

As a first example here is the view person_view_1 which contains all but id and ssn columns of table person. Users who have the right to read from this view but not from the table person don'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;

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!

Not only columns can be hidden in a view. It's also possible to hide 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 fulfill the condition.

-- No hit
SELECT *
FROM   person_view_3
WHERE  place_of_birth = 'Dallas';

-- One hit
SELECT *
FROM   person
WHERE  place_of_birth = 'Dallas';

This example uses the sum() function.

-- 
CREATE VIEW person_view_4 AS
  -- General hint: Please consider that not all columns are available 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.

Views can contain columns from several tables by using JOIN commands. The following example 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.

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.

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 a 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 a 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. Sometimes these rules are very fixed, in other cases they consist of flexible techniques like 'INSTEAD OF' 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 update-able in this sense:

• 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.
• Many implementations have a keyword which can be used to force a view to be read-only, even if technically it doesn't have to be.

If it is possible to use the UPDATE, INSERT or DELETE command to a view, the syntax is the same as with tables.

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 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 VIEW hobby_view_2 AS
  SELECT hobbyname, substr(remark, 1, 30) AS explanation
  FROM   hobby;
-- Verification
SELECT * FROM hobby_view_2;

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 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 a column of a table.
SELECT *
FROM   contact_view_4
WHERE  cnt > 1;

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;

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;

SELECT: Subquery

Structured Query Language Views

INSERT 1