View (database)

In a database, a view is the result set of a query stored on the data. It is a query that is presented as a (virtual) table from a set of tables in a relational database.

Views have the same structure as a table: rows and columns. The only difference is that only the definition is stored, not the data. Data retrieved by querying a view will be presented the same as data from a table. In fact, if you do not know that you are working with a view, there is nothing to suggest that this is the case. As with a table, data can be inserted, updated, deleted, and selected in a view. Although it is always possible to select data from a view, in some conditions there are restrictions on performing the rest of the operations on views.

A view is specified through a query expression (a SELECT statement) that calculates it and can be performed on one or more tables. On a set of relational tables you can work with any number of views.

Most DBMSs support the creation and manipulation of views. Views are created when multiple statements need to be made to return a final table.

Syntax of a view

In databases, a view is a query to one or more tables in a database that returns results without storing the data in a separate table. Views are often used to provide a "filtered" of the data in a database, for example, a view that only shows data from a table for a certain date range or for a certain set of column values. The syntax of a view is as follows:

CREATE VIEW name_vis AS (SELECT ♪ FROM Table)

CREATE VIEW view_name AS (SELECT * FROM table) means that a view called "< is being created. b>view_name" which reflects the results of the query in the table "table". The symbol " AS" indicates that the view is being defined in terms of another view or table. The symbol "()" indicates that the view is composed of a query. The symbol "*" indicates that the view returns all attributes of the table "table".

The basic elements of a view are:

• The name of the sight
• Tables or views of which are derived
• The query running for data
• The options of the query, such as filters or attributes that are returned

Equivalence

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

accounts_view:
-...
SELECT name,
money_received,
money_sent,
(money_received - money_sent) AS balance,
address,
...
FROM table_customers c
JOIN accounts_table a
ON a.customer_id = c.customer_id

Then the application could run a simple query like:

Simple query
-...
SELECT name,
balance
FROM accounts_view

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

Preprocessed query:
------------------
SELECT name,
balance
FROM (SELECT name,
money_received,
money_sent,
(money_received - money_sent) AS balance,
address,
...
FROM table_customers c JOIN accounts_table a
ON a.customer_id = c.customer_id)

The optimizer then removes unnecessary fields and complexity (for example: the address does not need to be read, since the main invocation does not use it) and then sends the query to the SQL engine for processing.