View vs Table
Views and tables, both are two database object types. In simple words, Views are stored or named select queries. They can be created as shown below.
Create or replace view view_name
As
Select_statement;
Tables are made up of columns and rows. A column is a set of data, which belongs to a same data type. A row is a sequence of values, which can be from different data types. Columns are identified by the column names, and each row is uniquely identified by the table primary key. Tables are created using “create table” DDL query.
Create table table_name (
Column_name1 datatype (length),
Column_name2 datatype (length)
….
….
….
);
Views
As mentioned before, each view’s body is a SELECT statement. Views are called as “Virtual tables” of the database. Though the views are stored in the database, they are not run until they are called using another SELECT statement. When they are called using SELECT statements, their stored SELECT queries are executed and show the results. Since views have only SELECT queries as their bodies, they do not need a large space. Here, are some benefits of views,
- Once the view is created, it can be called again and again using its name, without writing the SELECT query several times.
- Since these views are pre-compiled objects, its execution time is lesser than executing its SELECT query (Body of the view) separately.
- Views can be used to restrict the table data access. Therefore, they can be played an important role in data security, as well.
Tables
Table is a collection of rows. Rows can have data from different data types. Each row of the table must be identified by using a unique identifier (Primary key). Tables are the places where we store the data. INSERT, UPDATE, and DELETE queries can be used to insert a new row, update an existing row value and delete a row from the table. SELECT queries should be used to retrieve data from tables. Table structure also can be changed (if need) after it is created. ALTER TABLE queries should be used to change the table structure. Tables need more space than views to store its data content. There are several types of tables in databases.
- Internal tables
- External tables
- Temporary tables
|
What is the difference between Views and Tables? Views are virtual tables, which refer to SELECT queries, but tables are actually available in the database. Views do not need a large space to store its content, but tables need a large space than views to store its content. Views can be created using “create or replace” syntax. But tables cannot be created using “create or replace”, it must be “create table” syntax. Because table creation DDL does not allow replace. Table columns can be indexed. But view columns can’t be indexed. Because views are virtual tables. Table structure can be modified by using ALTER statements, but the structure of a view cannot be modified by using ALTER statements. (Views must be recreated to modify its structure) DML commands can be used to INSERT, UPDATE and DELETE records of tables, but DMLs are only allowed to updatable views, which do not have following in the view SELECT statement. Set Operators (INTERSECT, MINUS, UNION, UNION ALL) DISTINCT Group Aggregate Functions (AVG, COUNT, MAX, MIN, SUM, etc.) GROUP BY Clause ORDER BY Clause CONNECT BY Clause START WITH Clause Collection Expression in a Select List Sub query in A Select List Join Query
|