Difference between View and table in sql

Question

Possible Duplicate:
Difference Between Views and Tables in Performance

What is the main difference between view and table in SQL. Is there any advantage of using views instead of tables.

Possible duplicate of [Difference Between Views and Tables in Performance](http://stackoverflow.com/questions/4576589/difference-between-views-and-tables-in-performance) — Brian Tompsett - 汤莱恩, Oct 08 '16 at 20:08
Doesn't seem like a duplicate. The linked post is asking specifically about performance/efficiency. This post is asking more about how they differ as datatypes, as the selected answer represents. — Ð.., May 22 '19 at 14:04

score 302 · Accepted Answer · answered May 16 '11 at 09:15

302

A table contains data, a view is just a SELECT statement which has been saved in the database (more or less, depending on your database).

The advantage of a view is that it can join data from several tables thus creating a new view of it. Say you have a database with salaries and you need to do some complex statistical queries on it.

Instead of sending the complex query to the database all the time, you can save the query as a view and then SELECT * FROM view

answered May 16 '11 at 09:15

Aaron Digulla

321,842
108
597
820

24

So the view is actual a table that was made by a select statement. The view is stored and can be accessed so what would be the different between creating a new table with that information instead of a view. – Doug Hauf May 02 '14 at 21:40
40

You can think of a view as a "saved select statement" that you can repeat. It's not really a table; even though some databases allow to create views that have a real table beneath, it's really just a `SELECT` statement which returns results. – Aaron Digulla May 05 '14 at 08:46
1

something to note: some database system list views along with tables on "show tables;" command. – Dexter Oct 01 '14 at 15:37
1

@AaronDigulla A saved select statement? Does that mean every time we open the view the select statement is executed? If so, it is fair to assume views are always up to date? – Shwetabh Shekhar Aug 17 '17 at 05:56
3

@ShwetabhShekhar That depends on your database and the type of view. In the most simple form, the database will execute the SQL as if you had just sent the whole thing. In this case, you just save sending the many bytes again and again. Some databases can execute the query and store the result in a (real) table. Then you need to solve the problems somehow which you mentioned. Check the DB documentation how to do that. – Aaron Digulla Sep 04 '17 at 09:49
Is it a bad practice to have a view in a database which is a one-for-one mapping of a table with no selection or other operation performed? – FoxDeploy Nov 30 '17 at 17:40
2

@FoxDeploy Yes, since that view doesn't help in any way. Useful scenarios would be a view with limited permissions or a view which selects a table from another schema in the same database or another database (remote table). – Aaron Digulla Dec 08 '17 at 14:12
2

Just to add... some databases includes SAP HANA. Which treat ```views``` as a separate entity just like a normal table. These views always reflect the updated value in a particular tuple as it is in the Table over which the view was initially generated on. ```Views in HANA``` are further divided into Attibute Views, Analytic Views and Calculation Views. – Dynamic Remo Mar 01 '19 at 13:25

score 73 · Answer 2 · answered Aug 16 '16 at 01:54

Table: Table is a preliminary storage for storing data and information in RDBMS. A table is a collection of related data entries and it consists of columns and rows.

View: A view is a virtual table whose contents are defined by a query. Unless indexed, a view does not exist as a stored set of data values in a database. Advantages over table are

We can combine columns/rows from multiple table or another view and have a consolidated view.
Views can be used as security mechanisms by letting users access data through the view, without granting the users permissions to directly access the underlying base tables of the view
It acts as abstract layer to downstream systems, so any change in schema is not exposed and hence the downstream systems doesn't get affected.

score 38 · Answer 3 · edited Jan 28 '23 at 00:40

I thought this was best explained by the article "SQL - What is a View" published by 1Keydata:

A view is a virtual table. A view consists of rows and columns just like a table. The difference between a view and a table is that views are definitions built on top of other tables (or views), and do not hold data themselves. If data is changing in the underlying table, the same change is reflected in the view. A view can be built on top of a single table or multiple tables. It can also be built on top of another view. In the SQL Create View page, we will see how a view can be built.

Views offer the following advantages:

Ease of use: A view hides the complexity of the database tables from end users. Essentially we can think of views as a layer of abstraction on top of the database tables.

Space savings: Views takes very little space to store, since they do not store actual data.

Additional data security: Views can include only certain columns in the table so that only the non-sensitive columns are included and exposed to the end user. In addition, some databases allow views to have different security settings, thus hiding sensitive data from prying eyes.

score 8 · Answer 4 · answered Jun 18 '15 at 05:40

8

In view there is not any direct or physical relation with the database. And Modification through a view (e.g. insert, update, delete) is not permitted.Its just a logical set of tables

answered Jun 18 '15 at 05:40

Haris N I

6,474
6
29
35

2

In SQL Server, you can modify the underlying table through a view, if it only references one base table. [see here](https://msdn.microsoft.com/en-us/library/ms180800.aspx) – Kristen Hammack Aug 24 '16 at 17:02

score 5 · Answer 5 · answered Oct 07 '15 at 08:45

A view helps us in get rid of utilizing database space all the time. If you create a table it is stored in database and holds some space throughout its existence. Instead view is utilized when a query runs hence saving the db space. And we cannot create big tables all the time joining different tables though we could but its depends how big the table is to save the space. So view just temporarily create a table with joining different table at the run time. Experts,Please correct me if I am wrong.

score 3 · Answer 6 · answered Mar 07 '19 at 08:46

SQL Views:

View is a virtual table based on the result-set of an SQL statement and that is Stored in the database with some name.

SQL Table:

SQL table is database instance consists of fields (columns), and rows.

Check following post, author listed around seven differences between views and table

https://codechef4u.com/post/2015/09/03/sql-views-vs-tables

score 2 · Answer 7 · answered Mar 27 '17 at 07:59

Table:

Table stores the data in database and contains the data.

View:

View is an imaginary table, contains only the fields(columns) and does not contain data(row) which will be framed at run time Views created from one or more than one table by joins, with selected columns. Views are created to hide some columns from the user for security reasons, and to hide information exist in the column. Views reduces the effort for writing queries to access specific columns every time Instead of hitting the complex query to database every time, we can use view

Difference between View and table in sql

7 Answers7

Linked