This articleneeds additional citations forverification. Please helpimprove this article byadding citations to reliable sources. Unsourced material may be challenged and removed. Find sources: "View" SQL – news ·newspapers ·books ·scholar ·JSTOR(December 2023) (Learn how and when to remove this message)

In adatabase, aview is theresult set of a storedquery that presents a limited perspective of the database to a user.^[1] This pre-established query command is kept in thedata dictionary. Unlike ordinarybase tables in arelational database, a view does not form part of thephysical schema: as a result set, it is a virtual table^[1] computed or collated dynamically from data in the database when access to that view is requested. Changes applied to the data in a relevantunderlying table are reflected in the data shown in subsequent invocations of the view.

Views can provide advantages over tables:

• Views can represent a subset of the data contained in a table. Consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table.^[2]
• Views canjoin and simplify multiple tables into a single virtual table.^[2]
• Views can act as aggregated tables, where thedatabase engine aggregates data (sum,average, etc.) and presents the calculated results as part of the data.
• Views can hide the complexity of data. For example, a view could appear as Sales2020 or Sales2021, transparentlypartitioning the actual underlying table.
• Views take very little space to store; the database contains only the definition of a view, not a copy of all the data that it presents.
• Views structure data in a way that classes of users find natural and intuitive.^[2]

Just as afunction (in programming) can provideabstraction, so can a database view. In another parallel with functions, database users can manipulate nested views, thus one view can aggregate data from other views. Without the use of views, thenormalization of databases abovesecond normal form would become much more difficult. Views can make it easier to create lossless join decomposition.

Just asrows in a base table lack any defined ordering, rows available through a view do not appear with any default sorting. A view is a relational table, and the relational model defines a table as a set of rows. Since sets are not ordered — by definition — neither are the rows of a view. Therefore, anORDER BY clause in the view definition is meaningless; the SQL standard (SQL:2003) does not allow an ORDER BY clause in the subquery of a CREATE VIEW command, just as it is refused in a CREATE TABLE statement. However, sorted data can be obtained from a view, in the same way as any other table — as part of a querystatement on that view. Nevertheless, some DBMS (such asOracle Database) do not abide by this SQL standard restriction.

Views can be defined as read-only or updatable. If the database system can determine the reverse mapping from the view schema to the schema of the underlying base tables, then the view is updatable.INSERT,UPDATE, andDELETE operations can be performed on updatable views. Read-only views do not support such operations because the DBMS cannot map the changes to the underlying base tables. A view update is done by key preservation.

Some systems support the definition of INSTEAD OFtriggers on views. This technique allows the definition of other logic for execution in place of an insert, update, or delete operation on the views. Thus database systems can implement data modifications based on read-only views. However, an INSTEAD OF trigger does not change the read-only or updatable property of the view itself.

Variousdatabase management systems have extended the views from read-only subsets ofdata, particularlymaterialized views: pre-executed, non-virtual views commonly used indata warehousing. They give a static snapshot of the data and may include data from remote sources. The accuracy of a materialized view depends on the frequency of trigger mechanisms behind its updates.

Materialized views were introduced byOracle Database, whileIBM Db2 provides so-called "materialized query tables" (MQTs) for the same purpose.Microsoft SQL Server introduced in its 2000 version indexed views which only store a separate index from the table, but not the entire data.PostgreSQL implemented materialized views in its 9.3 release.

A view is equivalent to its source query. When queries are run against views, the query is modified. For example, if there exists a view named accounts_view with the content as follows:

-- accounts_view:-------------SELECTname,money_received,money_sent,(money_received-money_sent)ASbalance,address,...FROMtable_customerscJOINaccounts_tableaONa.customer_id=c.customer_id

then the application could run a simple query such as:

-- Simple query------------SELECTname,balanceFROMaccounts_view

The RDBMS then takes the simple query, replaces the equivalent view, then sends the following to thequery optimizer:

-- Preprocessed query:------------------SELECTname,balanceFROM(SELECTname,money_received,money_sent,(money_received-money_sent)ASbalance,address,...FROMtable_customerscJOINaccounts_tableaONa.customer_id=c.customer_id)

The optimizer then removes unnecessary fields and complexity (for example it is not necessary to read the address, since the parent invocation does not make use of it) and then sends the query to the SQL engine for processing.

• Bidirectionalization

• Database management systems
• SQL
• Database theory

• Articles with short description
• Short description is different from Wikidata
• Articles needing additional references from December 2023
• All articles needing additional references