In this article, we will discuss the difference between view and table. Both of these terms are commonly used in relational database.
What is a view?
A view is a result of a SQL query. The result look like a table, however this table is not physically present in the database, rather the data displayed as a view is fetched from the tables in database. This is why view is often referred as virtual table.
Syntax for creating a view:
Create view view_name as select column_list from table_name
Example:
CREATE VIEW [Senior Employees] AS SELECT Emp_Name, Emp_Age FROM Employees WHERE Emp_age >= 60;
Here we are creating a view (virtual table) with the name "Senior Employees"
and this virtual table contains the employee name and age records of those employee who are older than 60 years. This data is fetched from the table Employees
.
Employees table:
Emp_Id Emp_Name Emp_Age Emp_City Emp_Dept 101 Tom 55 Noida Sales 102 Ron 60 Delhi Sales 103 Ajeet 61 Gurgaon Retail 104 Carl 59 Noida HR 105 Daniel 65 Agra Manager
Senior Employees view:
Emp_Name Emp_Age Ron 60 Ajeet 61 Daniel 65
What is a table?
A table contains the data in form of rows and columns. For example, if a student table contains records of 100 students and details of each student consists of student name, id, age and address, then the student table should have 100 rows and 4 columns.
The columns are the attributes of the records such as student name, id age and address and each student record is stored in a row so 100 rows for 100 students.
Syntax for creating a table:
CREATE TABLE table_name ( column_name1 datatype, column_name2 datatype, column_name3 datatype, .... );
View vs Table
View | Table |
---|---|
View is generated from a table. Its data depend on the data present in the underlying tables. | Table data is inserted through queries and it doesn’t depend on anything for getting the data, rather the data is inserted by user. |
View existence is limited for a single query, its existence is temporary. Every time you run a SQL query to create view, it gets recreated using the existing data of tables. | Tables are permanently stored in database, until they are deleted using SQL queries. |
You cannot insert, delete or update data of a view. | You can insert, delete or update data of tables. |
A view can be easily recreated and populated with different data using replace view command. | A table cannot be recreated. You need to delete the existing table to create a new table with the same name. |
A view can contain data from multiple tables. | A table maintain the relationship with other tables using foreign key. |