39  SQL views

Views allows you to store your select queries in databases. It is very useful for abstracting especially common and complex queries. The following figure shows a view that includes columns from multiple tables:

view example multiple columns from multiple tables

Views are common concept in databases and all databases supports it. See documentation for our example databases.

simple lab

  1. give Chinook database example, join query
SELECT A.Name AS ArtistName
, B.Title AS AlbumTitle
, T.Name  AS TrackName
FROM Artist A
INNER JOIN Album B
ON A.ArtistId  = B.ArtistId 
INNER JOIN Track T ON T.AlbumId  = B.AlbumId
  1. create view name as select statement
CREATE VIEW ArtistTracks
AS
SELECT A.Name AS ArtistName
, B.Title AS AlbumTitle
, T.Name  AS TrackName
FROM Artist A
INNER JOIN Album B
ON A.ArtistId  = B.ArtistId 
INNER JOIN Track T ON T.AlbumId  = B.AlbumId
  1. select from view.
select * from ArtistTracks;

note

Common example in the books is following: In client-server databases, it could also be used to control access. Instead of giving access to tables, you could give access to view itself. I have never seen this usage.

note

Views could be target of insert statements if rows in the views correspond to 1-to-1 to underlying tables, for example aggregate queries will never work.

Sqlite allows this feature when the view is single table only.

I dislike this feature and never uses it.

39.1 SQL Views materialized

Normally, selecting from a view is not different than using view query in subquery from statement. Due to performance reasons, you may need results from views to be faster. SQL Server and Oracle offers materialized views for this purpose. Sql Server uses both indexed and materialized view for its terminology. Indexed could be added Materialized views to make them more faster.

Other databases like postgre sql, IBM DB2 and Sybase SQL Anywhere also support this concept

How frequently results are updated on physical storage is called refresh frequency. These types of views could have problems with frequently updated tables but they are very useful for aggregate queries.

39.2 SQL System Views

39.2.1 Schema views

System views give dynamic information about the database system itself.

The most useful example to system views are SQL Standard Information schema views: These views are used to get table, column, view and other object information from databases.

  • INFORMATION_SCHEMA.TABLES
  • INFORMATION_SCHEMA.COLUMNS
  • INFORMATION_SCHEMA.VIEWS
  • and others

See database specific documentation for this very useful feature from SQL Standard. Information schema views are supported by Sql server, mysql, postgre sql and other databases. Unfortunately, both sqlite and oracle does not support this standard. See following wikipedia page to see which databases support it.

This information are also accessible by database specific tables.

39.2.2 Performance views

There are also system views for system performance usage in databases, to see which operations are very CPU intensive, very long, etc.