What is the Difference Between View and Table

The main difference between view and table is that view is a virtual table based on the result set of an SQL statement, while a table is a database object that consists of rows and columns that store data of a database.

Generally, RDBMS is a software that helps to create and manage databases which are designed according to the relational model. Additionally, MySQL, MSSQL etc. are some popular RDBMSes. A developer can create various operations on the data stored in databases using the Structured Query Language (SQL). It is possible to insert, update, select and delete data as well as perform advanced operations such as stored procedures and triggers using SQL. Furthermore, in the context of RDBMS, there are various database objects. Two of them are view and table. Overall, the view depends on the table. In other words, there should be one or multiple tables to create views.

Key Areas Covered

1. What is View
      -Definition, Functionality
2. What is Table
     -Definition, Functionality
3. Difference Between View and Table
     -Comparison of key differences

Key Terms

SQL, Table, View

What is View

The view is a logical subset of data of one or multiple tables. It helps to restrict data access. A view contains rows and columns similar to a table. Moreover, the fields or the records of the view can be from one or more tables in the database.

For example, assume creating the below view.

CREATE VIEW [Sydney Employees] AS

SELECT id, name

FROM employee

WHERE city= “Sydney”

It is possible to query the above view as follows.

SELECT * FROM [Sydney Employees];

Additionally, a programmer can delete the view using the below SQL statement.

DROP VIEW [Sydney Employees];

It is also possible to use update a view.

Furthermore, there are two types of views. They are the simple view and complex view. Programmer can create a simple view from a single table. It does not contain groups of data, and there is no use of functions. Finally, the complex view is created from one or more tables. Moreover, it contains groups of data and functions.

What is Table

RDBMS consists of multiple databases, and each database has one or more tables. A table is a database object that consists of rows and columns. A programmer has to create a table inside a specific database. He can create a table using the “CREATE” command. The columns represent the properties while rows represent records. Each column has a data type. Therefore, it is only possible to store values of the defined data type.

For example, assume a table called employee. The SQL statement to create the table is as follows.

CREATE employee(

            id int not null primary key,

            firstname varchar(255),

            lastname varchar(255),

            salary double,

            city varchar(255)

);

The above SQL statement creates a table called employee. It has five columns which are id, firstname, lastname, salary and city. The id is int, salary is double, and the other columns store varchar values. Furthermore, id is the primary key, and it does not store null values.

Additionally, it is possible to delete an existing table for the database using ‘DROP’.

DROP table employee ;

The above statement deletes the employee table from the database.

Furthermore, the programmer can use ‘ALTER’ command to add, modify and delete the columns of an already existing table.

Difference Between View and Table

Definition

A view is a database object that allows generating a logical subset of data from one or more tables, while a table is a database object or an entity that stores the data of a database. Thus, this explains the main difference between view and table.

Basis

Moreover, the view is a virtual table, whereas the table is an actual table.

Dependency

Furthermore, the view depends on the table, while the table is an independent data object. Hence, this is another difference between view and table.

Conclusion

In brief, view and table are two database objects. The main difference between view and table is that view is a virtual table based on the result set of an SQL statement, while the table is a database object which consists of rows and columns that store data of a database. In brief, a programmer cannot create views without using tables.

References:

1.“SQL Views.” SQL CREATE VIEW, REPLACE VIEW, DROP VIEW Statements, Available here.
2.“SQL CREATE VIEW Keyword.” SQL CREATE VIEW, Available here.
3.“SQL VIEW.” Studytonight, Available here.

Image Courtesy:

1.”Relational database terminology” By User:Booyabazooka – Own work (Public Domain) via Commons Wikimedia