View (SQL): Difference between revisions

Revision as of 10:38, 2 December 2020

Latest revision as of 10:04, 10 August 2025

(24 intermediate revisions by 21 users not shown)

Line 1:

{{Short description|Database stored query result set}}

{{Unreferenced|date=March 2020}}

{{More citations needed|date=December 2023}}

A view is is like a window through which data from tables can be viewed or changed. The table on which is based is called best table. The view is Tod as a SELECT statement in the data dictionary.

In a [[database]], a '''view''' is the [[result set]] of a ''stored'' [[Query language|query]] onthat thepresents [[data]],a whichlimited perspective of the [[database]] usersto cana queryuser.<ref justname="mysql-views">{{cite asweb they| wouldurl=https://dev.mysql.com/doc/refman/8.0/en/views.html in| title=25.5 Using Views | author=<!--Not stated--> | date=2023-12-12 | website=MySQL | publisher=Oracle | access-date=2023-12-12 | quote=Views are stored queries that when invoked produce a persistentresult databaseset. collectionA objectview acts as a virtual table. | archive-url=https://web.archive.org/web/20231123070115/http://dev.mysql.com/doc/refman/8.0/en/views.html | archive-date=2023-11-23 | url-status=live }}</ref> This pre-established query command is kept in the database[[data dictionary]]. Unlike ordinary ''[[base tablestable]]s'' in a [[relational database]], a view does not form part of the [[database design|physical schema]]: as a result set, it is a virtual table<ref name="mysql-views" /> computed or collated dynamically from data in the database when access to that view is requested. Changes applied to the data in a relevant ''underlying table'' are reflected in the data shown in subsequent invocations of the view. In some [[NoSQL]] databases, views are the only way to query data{{how|date=December 2019}}.

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.<ref name="SQL-reference-groff-weinberg">{{cite book

| last1 = Groff

* Views can [[Join (SQL)|join]] and simplify multiple tables into a single virtual table.

| first1 = James R.

| last2 = Weinberg | first2 = Paul N.

| date = 1999

| title = SQL: The Complete Reference

| url = http://englishonlineclub.com/pdf/SQL%20-%20The%20Complete%20Reference%20[EnglishOnlineClub.com].pdf

| ___location = <!-- not stated -->

| publisher = Osborne/McGraw-Hill

| pages = 291–292

| isbn = 0072118458

}}</ref>

* Views can [[Join (SQL)|join]] and simplify multiple tables into a single virtual table.<ref name="SQL-reference-groff-weinberg" />

* Views can act as aggregated tables, where the [[database engine]] aggregates data ([[summation|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 Sales2000Sales2020 or Sales2001Sales2021, transparently [[Partition (database)|partitioning]] 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.<ref name="SQL-reference-groff-weinberg"/>

* Depending on the [[SQL]] engine used, views can provide extra security.

Just as a [[function (computing)|function]] (in programming) can provide [[Abstraction (computer science)|abstraction]], 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, the [[Database normalization|normalization]] of databases above [[second normal form]] would become much more difficult. Views can make it easier to create lossless join decomposition.

Line 16 ⟶ 26:

== Read-only vs. updatable views ==

Database practitionersViews can definebe viewsdefined as [[file system permissions|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 (SQL)|INSERT]], [[Update (SQL)|UPDATE]], and [[Delete (SQL)|DELETE]] 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 OF [[Database trigger|triggers]] 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.

==Materialized views==

{{detailsfurther|Materialized view}}

Various [[database management system]]s have extended the views from read-only subsets of [[data]], particularly [[materialized view]]s: pre-executed, non-virtual views commonly used in [[Data warehouse|data 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 by [[Oracle Database]], while [[IBM DB2Db2]] 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.

== Equivalence ==

Line 72 ⟶ 82:

</syntaxhighlight>

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.

== See also ==

* [[Bidirectionalization]]

== External links ==

* [http://msdn.microsoft.com/en-us/library/ms187956.aspx Views in Microsoft SQL Server]

* [http://dev.mysql.com/doc/refman/5.7/en/views.html Views in MySQL]

* [http://www.postgresql.org/docs/current/interactive/tutorial-views.html Views in PostgreSQL]

* [http://www.sqlite.org/lang_createview.html Views in SQLite]

* [http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_8004.htm#SQLRF01504 Views in Oracle 11.2]

* [https://web.archive.org/web/20111011082405/http://wiki.apache.org/couchdb/Introduction_to_CouchDB_views Views in CouchDB]

* [http://doc.nuodb.com/display/doc/CREATE+VIEW Views in NuoDB]

{{Databases}}

==References==

{{Reflist}}

{{DEFAULTSORT:View (Database)}}