Folks relate to physical tables; even the most non-relational-minded person can picture a fixed structure file and equate that to a table and its columns. The spreadsheet image is ubiquitous. DBMS-defined views are logically similar to tables and in usage are certainly interchangeable with tables.
Occasionally, you hear one person or another talking about updating data within a view or worrying about the timing of such updates. But unless a view is a materialized view, there is no timing or even populating of data for a view.
A view is a virtual construct. Similar to a table, a view has a name and columns, but for a view, those items are simply entries inside the metadata of the DBMS documentation. Behind a view name is a SQL SELECT statement and nothing more. When someone writes a new query using the view as the FROM clause object, the magic within the optimizer of the DBMS takes that new query, and the previously defined query constituting the named view, and merges those bits of SQL together for execution. The data is only brought together as the DBMS works through providing the final query results.
Materialized Views
While views have always been useful, there are times when views suffer from the sin committed by many SQL SELECT statements, in that they take “too long” to execute. The shining conceptual knight arising to slay the slow-performing view dragon is the materialized view, although it has several names across the various relational DBMS products. The materialized view is defined, similar to a view, but then the DBMS tries to manage behind the scenes the ETL work basically needed to create a physical table. And being physical, queries made of this “view” are now executing against a physical structure having all the data already resolved. Speed of performance is about the best one will get. The materialized view then does have data, so now there is a need to worry over when such data is refreshed.
Some DBMSs have so many convoluted restrictions over the ins and outs of materialized views that they almost never can be used. But even when such restrictions are not overly constrictive, we are dealing with a table, not a view. Certainly, the development team does not need to worry about the population of this table, but is that really a good circumstance?
Ultimately, the materialized view qualifies as little more than a DBMS-based rendition of a cute pet trick. If a table is needed to improve performance, just build a table and be done with it.
The Rich Landscape of an Application Database
True views are nothing more than named SQL SELECT queries. As such, they are a shorthand—a way of reusing the logic surrounding SQL statements. Complex joins or calculations no longer need to be remembered. Fundamental views do not, of themselves, store data, nor do they need refreshing. Views return whatever the current data is from the tables that are referenced by the view’s defining SQL SELECT statement.
The concept of relational closure includes the side effect of “everything is a table so the results of any SELECT statement are basically a table." Therefore, a results table from one query is perfectly acceptable as an object in the FROM clause of another query, or an item in a SELECT list, or even a value in a WHERE clause. This ubiquitous table flexibility is much of the power behind SQL. Blending a combination of physical tables containing the detailed level data, then adding in a mixture of useful summaries or other combinations via views, creates the rich landscape of an application database.