“Ordinary” viewsThese are most commonly referred to as “views”. Most databases allow for declaring them using this syntax
These stored views are then part of the catalog and can be referenced by name just like tables, which is great for re-use. And what’s even greater, you can grant a different set of privileges to views than to tables, which allows you to implement a complete security layer only using views (e.g. hiding some columns, or rows from certain users) Some databases (including Oracle, PostgreSQL) even allow for updating them under certain circumstances – mostly when they’re unambiguous, 1-1 mappings of a single table that does not produce any calculations or denormalisations.
CREATE VIEW my_view AS SELECT col1, col2 FROM my_table WHERE ...
Materialized viewsJust like the above “ordinary views”, materialized views can be used just like tables. In fact, they are tables as their data is materialized on disk, updated whenever their content is updated. These are useful for frequent, complex queries on rarely updated data. Just add the
MATERIALIZEDkeyword and you’re set:
Among others, Oracle and PostgreSQL support materialized views. Other databases like SQL Server know of “indexed views”, which are a bit less powerful as you have to explicitly “materialise” your view data in indexes.
CREATE MATERIALIZED VIEW my_view AS SELECT col1, col2 FROM my_table WHERE ...
“Snapshot” viewsThese aren’t really views, they’re real tables. But in the context of this blog post, you could think of them as a permanently materialized “snapshot” view of your data. You can create such views using different syntaxes: Most databases, e.g. Oracle
Some databases, e.g. SQL Server
CREATE TABLE my_view AS SELECT col1, col2 FROM my_table WHERE ...
The nice thing about this approach is the fact that like materialized views, these “views” can be very useful for frequent querying – you have to pre-calculate the data only once. But once you’ve calculated that data, you generate a “snapshot” of it, and the data can continue to live independently from your view – just like a snapshot! (don’t forget to add relevant indexes, though) Note that some databases including DB2 and Oracle support real SQL:2011 standard “snapshots”, such as flashback query in Oracle, or time travel queries in DB2. That’s a different story, though.
SELECT col1, col2 INTO my_view FROM my_table WHERE ...
Parameterized viewsFew people refer to these views as “views”, but if you think about it, that’s what they really are. Table-valued functions are stored procedures that return tables that can then again be used in SQL. For example (using PostgreSQL syntax):
CREATE FUNCTION my_view (arg1 INTEGER, arg2 INTEGER) RETURNS TABLE ( col1 INTEGER col2 INTEGER ) AS $$ BEGIN RETURN QUERY SELECT col1, col2 FROM my_table WHERE v1 = arg2 AND v2 = arg2; END $$ LANGUAGE plpgsql;
That’s quite powerful, isn’t it? Among others, Firebird, HANA, HSQLDB, Oracle, PostgreSQL, SQL Server support table-valued functions.
SELECT * FROM my_view (42, 1337) WHERE ...
Common Table ExpressionsLike ordinary views, these views are named but they’re scoped only for a single statement – mostly a SELECT statement, although PostgreSQL or SQL Server also allow for common table expressions to be used with other DML statements. These “views” can be written as such:
While common table expressions are very useful for structuring code (they’re like “table variables”), they come with a price in Oracle or PostgreSQL, as the view is most often temporarily materialized, which prevents a lot of SQL transformations in the optimiser. On the flip side, common table expressions can be recursive / hierarchical, which is great for graph / tree traversal.
WITH my_view_a AS ( SELECT ... ), my_view_b AS ( SELECT ... ) -- To be consumed immediately by a statement SELECT * FROM my_view_a, my_view_b
Derived tablesThe most common type of views (although rarely called “views”) are derived tables, i.e. all nested select statements that are put in a FROM clause. E.g.:
Unlike common table expressions, derived tables cannot be reused easily within a statement, but chances are high that they can be optimised into a different statement that has a higher performance.
SELECT * FROM ( SELECT ... ) my_view_a, ( SELECT ... ) my_view_b
ConclusionSQL is all about tables and recomposition of tables in ad-hoc queries. The most important clause of any SQL statement is the
FROMclause. It specifies the set of tuples that you want to recompose, filter, group, project in various ways. As we have seen above, you can feed any such table transformation easily into yet another transformation via one of the above ways to create views. Curious about more? Read our popular article “10 Easy Steps to a Complete Understanding of SQL“