{"id":315,"date":"2016-05-24T21:41:26","date_gmt":"2016-05-25T04:41:26","guid":{"rendered":"https:\/\/database.guide\/?p=315"},"modified":"2023-02-14T12:46:00","modified_gmt":"2023-02-14T02:46:00","slug":"what-is-a-view","status":"publish","type":"post","link":"https:\/\/database.guide\/what-is-a-view\/","title":{"rendered":"What is a View?"},"content":{"rendered":"

In the world of\u00a0databases,\u00a0a view<\/dfn> is a query that’s stored on a\u00a0database<\/a>.<\/p>\n

The term can also be used to\u00a0refer to\u00a0the result set<\/em> of a\u00a0stored\u00a0query.<\/p>\n

To create a view, you write a query, then save\u00a0it as a view.<\/p>\n

To run\u00a0a\u00a0view, you\u00a0query it, just like you’d query\u00a0a table. The difference is that, the view itself is a query.\u00a0So\u00a0when you query the view, you’re effectively querying a query. \u00a0This enables you to save complex queries as views, then run\u00a0simple queries against those views.<\/p>\n

<\/p>\n

Views are sometimes referred to as virtual tables, because they present data in the form of a table<\/a>, but without such a table existing in the database.<\/p>\n

A view can present data from multiple tables and present it\u00a0as though it’s in a single table (just like any other SELECT<\/code> query). \u00a0So when creating a view (just as creating any SELECT<\/code> query), you specify which columns<\/a> to display.<\/p>\n

Creating a View<\/h2>\n

Views are created using the (SQL) CREATE VIEW<\/code> statement. So for example, to create a view called say, “NewCustomers” you would start with:<\/p>\n

CREATE VIEW NewCustomers AS<\/pre>\n
Followed by the query that makes up the view.<\/p>\n
Here’s an example of a view that has just been created in SQL Server<\/a>:<\/p>\n
\"Screenshot<\/a>
A SQL view has just been created, and now appears in the Object Explorer under the “Views” node.<\/figcaption><\/figure>\n
Querying\u00a0a View<\/h2>\n
You “run” a view by\u00a0querying it, just as you’d query a table. So, for example, you could perform a SELECT<\/code> query against the NewCustomers view like this:<\/p>\n
SELECT * FROM NewCustomers<\/pre>\n
You can also narrow the results down, just as you can with a normal query. So, you could also do this, for example:<\/p>\n
SELECT * FROM NewCustomers\r\n\r\nWHERE OrderValue > $150\r\n<\/pre>\n
Here’s an example of querying the\u00a0view that was created in the previous screenshot:<\/p>\n
\"Screenshot<\/a>
Run a view by using a SELECT statement.<\/figcaption><\/figure>\n
Updatable Views<\/h2>\n
Some views can be used to update data. So in this case,\u00a0you can use an INSERT<\/code>, UPDATE<\/code>, or DELETE<\/code> statement to modify the data in the underlying base tables.<\/p>\n
However, there are many cases where you can’t\u00a0update data through a view, for example, if it’s returning aggregate data (such as SUM()<\/code>, AVG()<\/code>, etc).\u00a0It’s best to check\u00a0the\u00a0documentation for your DBMS to see what\u00a0restrictions are placed\u00a0on updatable views.<\/p>\n
Advantages of Views<\/h2>\n
Views provide several\u00a0advantages over tables. Here are some of the main benefits of views over tables:<\/p>\n
Simplified Code<\/h3>\n
Views\u00a0can\u00a0allow you to write simpler queries. So, rather than running a complex query against a table, you can run a simple query against a view (which contains the complex query).<\/p>\n
Enhanced Security<\/h3>\n
Views can restrict access to only the data that a given user is authorised to access.<\/p>\n
For example, you could give a user access to a view, but not the underlying table\/s that the view queries. Therefore, the user will only have access to the columns defined in the view.By excluding certain columns in the view, you are denying the user access to the data in those columns.<\/p>\n
Better Usability<\/h3>\n
Views can present data in a simplified way.\u00a0So data can be presented in a way that makes more sense to the user\u00a0(rather than imposing the underlying table structure on them).<\/p>\n
Better\u00a0Maintainability<\/h3>\n
Views can also help in cases where the table structure is changed.<\/p>\n
If a\u00a0database’s table structure is changed, a view can make this seamless to the user. Users can continue to query the view without knowing the underlying tables have been changed.\u00a0In this case, the view could be updated to query the new table structure, while returning the same “virtual table” to the user, along with the same column names.<\/p>\n","protected":false},"excerpt":{"rendered":"
In the world of\u00a0databases,\u00a0a view is a query that’s stored on a\u00a0database. The term can also be used to\u00a0refer to\u00a0the result set of a\u00a0stored\u00a0query. To create a view, you write a query, then save\u00a0it as a view. To run\u00a0a\u00a0view, you\u00a0query it, just like you’d query\u00a0a table. The difference is that, the view itself is a … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[18,166,167,93,24,20],"class_list":["post-315","post","type-post","status-publish","format-standard","hentry","category-database-concepts","tag-database-terms","tag-define","tag-definition","tag-functions","tag-views","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/315","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=315"}],"version-history":[{"count":9,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/315\/revisions"}],"predecessor-version":[{"id":472,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/315\/revisions\/472"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=315"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=315"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=315"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}