The beauty and joy of a relational database is the concept of relational closure—everything is a table. Beyond the eponymous table, query results are also “tables.” Any query serves as a table to be queried by any other query, which is why queries may be nested almost infinitely within queries.
Anyone who has seen a ten- or twenty-page SQL SELECT statement can attest to this SQL mosaic-ability. Sadly, this simple and elegant aspect leads to confusion among many who should know better.
While everything looks and acts as if it were a table, being a “table” does not mean there is data physically stored as rows and columns. A view, in fact, is nothing more than a SELECT query. The query becomes special in that it is given a name. The view name, once defined, database users may reference this view as if it were a physical table. Whenever the controlling mechanism within the database sees a query against a view, it simply drags out the SQL behind the view and plops it into the appropriate place for execution and resolution.
But other than having the SELECT statement SQL text buried away amongst the database’s metadata, the data one sees when retrieving content from that view is not physically stored as part of that view. If a view is defined from a query joining three tables, those three tables have their data physically stored. And it is only as the view is invoked that the database brings that data together in core for presenting to the query invoker.
Data users writing queries pulling data from here, there, and everywhere need not worry or concern themselves about whether their data is provided directly from a table or indirectly through a view. However, those individuals building, maintaining, or even quality investigating, need to understand these distinctions.
Asking to “refresh data” from a view, is something that might confuse others since there is no there, there. While the more bucolic data farmers can make such requests, data engineers, or data scientists should be more database-object-aware. If such a data fresh request is made, the request dumps onto the shoulders of others the task of tracking down which tables participate in providing some of the view content, and further evaluating whether all or some of these base tables need to be “refreshed.” These are tasks that a data engineer should know how to do.
Adding to possible confusions, there is an object supported almost everywhere called a “materialized view.” Effectively, this “materialization” is building physical storage structures internally through the DBMS rather than by someone writing code. These materialized views may be, and need to be, refreshed at times to obtain current data. But being materialized, this view is really a table and no longer a view, regardless of someone cleverly calling it a kind of view.
A view is best considered a shortcut providing a potentially large SQL SELECT query with only the mention of the view name. It allows for reusing potentially complex join and other business logic without having to re-type it again, and without having to worry over remembering this logic correctly.
The view already “knows.” One just writes a new query pulling out what is desired from the larger context of the view. The mix of both tables and views creates the larger inventory of objects available for data users to query and gather the items needed for their reporting, analytics, and mining. The more complex views serve as helpers to business queriers providing ease of use by already resolving more tedious and repetitive queries elements.