You probably know about “ordinary views” already, but I’m sure you’ll find one or two things in this article that you haven’t thought about in this way yet…
What exactly are SQL views?
Views in SQL are a means of treating complex queries in the same way as “ordinary” tables. In fact, SQL is all about tables (which are bags of records), much like relational algebra is all about relations (which are sets of tuples).
There are different types of views:
These are most commonly referred to as “views”. Most databases allow for declaring them using this syntax
CREATE VIEW my_view AS SELECT col1, col2 FROM my_table WHERE ...
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.
Just 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
MATERIALIZED keyword and you’re set:
CREATE MATERIALIZED VIEW my_view AS SELECT col1, col2 FROM my_table WHERE ...
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.
These 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
CREATE TABLE my_view AS SELECT col1, col2 FROM my_table WHERE ...
Some databases, e.g. SQL Server
SELECT col1, col2 INTO my_view 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)
Few 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;
SELECT * FROM my_view (42, 1337) WHERE ...
That’s quite powerful, isn’t it? Among others, Firebird, HANA, HSQLDB, Oracle, PostgreSQL, SQL Server support table-valued functions.
Common Table Expressions
Like 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:
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
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.
The 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.:
SELECT * FROM ( SELECT ... ) my_view_a, ( SELECT ... ) my_view_b
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.
SQL is all about tables and recomposition of tables in ad-hoc queries. The most important clause of any SQL statement is the
FROM clause. 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“