Table of contents
1.
Introduction
2.
Table
2.1.
Syntax
2.1.1.
Creating a Table
2.1.2.
Inserting values in a Table
2.1.3.
Viewing a Table
2.2.
Example
2.2.1.
Output
3.
View
3.1.
Syntax
3.2.
Example
3.2.1.
Output
4.
Comparison between Table and View
5.
Frequently Asked Questions
5.1.
What is the difference between a view and a table in SQL?
5.2.
What is the use of views in SQL?
5.3.
Are tables faster than views?
5.4.
Are views stored physically?
5.5.
Can a view have multiple tables?
6.
Conclusion
Last Updated: Mar 27, 2024
Easy

Difference Between Table and View

Author Pallavi singh
0 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

Hello, ninjas! You must have encountered terms like table, database, SQL, etc. In this article, we will study the difference between Table and View in a relational database.

Table vs View

SQL(Structured Query Language) is used to access and manipulate databases. Table and View are vital parts of a relational database and have their features.

Let us first study a Table in a Database.

Table

In a relational database, data is stored as ordered rows and columns. That database object is called a Table. The name of a table must be unique in a database. Also, the name of the table must not be a SQL keyword. The table's name is not case-sensitive and should not match any other object's name in the schema.

Each column in a database represents the attribute of that table. Each row in a database represents a set of attribute values of that table. Each column(attribute) has a name and associated data type.

Syntax

Let us look at the syntax for creating a table, inserting values in it and how to view the table.

Creating a Table

CREATE TABLE table_name(
	column_declaration_1,
	column_declaration_2,
	column_declaration_3,
	……,
);


Inserting values in a Table

INSERT INTO table_name (column_list) VALUES 
	(values_row_1),
	(values_row_2),
	 …..
	(values_row_n);


Viewing a Table

This syntax is for viewing the whole table.

SELECT * FROM table_name;


This syntax is for viewing specific columns of the table.

SELECT column_1, coumn_2,….. FROM table_name;

Example

Let us look at a table named “univ” shown below:

Example table

It is a table with four attributes:-

  • s_no:- It represents the overall numbering of the rows of the table.
     
  • name:- It represents the name of the student.
     
  • course:- It represents the name of the course the student is studying. 
     
  • roll_no:- It represents the roll number of the student.


If we have to create the table "univ" in a relational database, we will use the following commands:-

CREATE TABLE univ(
  s_no int,
  name varchar(20),
  course varchar(40),
  roll_no int);
INSERT INTO univ VALUES
	(1, "Michael", "Chemistry", 74),
	(2, "Maze", "Physics", 75),
	(3, "Priya", "Mathematics", 76),
	(4, "Rachel", "Mathematics", 77);
SELECT * FROM univ;

Output

mysql output of table

Also see, TCL Commands in SQL

View

As the name suggests, View is a query in SQL that provides information about one or more tables. It is like looking into the database through a window. A view is a virtual table formed when we run a query. The data of a view is that of the table whose data it extracts and presents; that table is called a base table.

A view does not occupy physical space on the system.

Syntax

CREATE VIEW view_name AS
SELECT column_name_1, column_name_2,...
FROM table_name
WHERE condition;

Example

We can create a view named "first" for the table above, selecting only the attributes “name” and “course” with the condition that the “roll_no” should be 74.

We can create and display the above-stated View using the following commands:

CREATE VIEW first AS
SELECT name, course
FROM univ
WHERE roll_no=74;

SELECT * FROM first;

Output

mysql output of view

Also see, SQL EXCEPT

Comparison between Table and View

Let us look at the differences between table and view:-

S.No.

Table

View

1

A table is a database entity that stores data in the form of rows and columns.

A view is a virtual table used to view or manipulate some parts of the table. It also has rows and columns as real ordered tables.

2

A table stores the data.

A view only extracts data from the table.

3

A table can only be created or dropped.

A view can be recreated.

4

A table is an independent database entity.

A view is dependent on the table.

5

It is stored in physical storage as it occupies real space on systems.

It is not stored physically. It only requires some space in memory whenever we run its query.

6

A table gives results faster.

A view gives results slower because it has to run its queries each time to retrieve the information from the table.

7

We can add, delete or update the data in a table.

We cannot modify data from a view. We can change the data in the base table.

You can also read about, loop and while loop, AMD vs Intel

Frequently Asked Questions

What is the difference between a view and a table in SQL?

A table in a relational database consists of rows and columns which store data. A view, in simple terms, can be defined as a virtual table that is drawn out from a database.

What is the use of views in SQL?

Views can be used as security measures by displaying the data to the users without allowing them to edit them. View simplifies and focuses on each user's perception of the database.

Are tables faster than views?

Since tables directly store data, they are faster to access. View, on the other hand, can take longer than tables to access data.

Are views stored physically?

A VIEW does not require physical storage since it is just a virtual table that does not exist physically.

Can a view have multiple tables?

Yes, a view can have multiple tables and is able to show information from multiple tables together.

Conclusion

Relational databases store data in the form of rows and columns. Table and View are both integral parts of these kinds of databases. In this article, we studied the concepts of a Table and a View and their differences in relational databases.

So its time for you now to refer to some other articles which are based on a similar topic, i.e., Difference between Table and View:

We hope this blog has helped you enhance your Table and View knowledge. Do visit here to study database management systems in depth and clarify all your concepts. Also, try Coding Ninjas Studio to practice programming problems for your complete interview preparation. Also, check out Top 100 SQL Problems - Coding Ninjas to get hands-on experience with frequently asked interview questions and land your dream job.

To learn more about DSA, competitive coding, and many more knowledgeable topics, please look into the guided paths on Coding Ninjas Studio. Also, you can enroll in our courses and check out the mock test and problems available to you. Please check out our interview experiences and interview bundle for placement preparations.

Happy Coding!

Live masterclass