Lably
Skip to content

View (SQL)

  • 5 min read
  • 3 chapters

Related on Lably

Through Database, Query language and Relational database

Lab · Class 11–12Databases & SQLLab · Class 6–12AI & Python Builds (Medha AI)LabRelational Databases

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.
  • Views can join and simplify multiple tables into a single virtual table.
  • Views can act as aggregated tables, where the database 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, transparently 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.

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

Just as rows 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, an ORDER 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 query statement on that view. Nevertheless, some DBMS (such as Oracle Database) do not abide by this SQL standard restriction.

Read-only vs. updatable views

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, and 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 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.

Sign in to keep reading

2 more chapters are free with a Lably account.

More in Computing and communication

ISBNUnique numeric book identifier since 1970X (social network)American social networking serviceWayback MachineDigital archive by the Internet ArchivePython (programming language)General-purpose programming languageMacOSOperating system for Apple computersUnicodeCharacter encoding standard