View vs table in SQL, what are the differences?

Views and tables are common ways of organising data in a SQL database, but when should you use one or the other? In this post I explain what they are, their differences, and help you decide which is best to use for your use case.

What are views and tables in a SQL database?

To start with, let’s lay some groundwork and first define what tables and views are.

What is a SQL table?

A table is the essential building block of a SQL database. It is a store of data which can be updated, replaced, and most importantly queried. Typically, tables are organised into a star schema made up of dimension and fact tables.

What is a SQL view?

A view is a virtual table that doesn’t store any data, but instead runs a defined SQL query to get data from one of more tables in a database. It’s best used when you need to access the same transformed data regularly, or when you want to create a ready made dataset for end users.

What’s the difference between tables and views in SQL?

Views and tables both return data when queried, but they are fundamentally different objects in SQL. A view is an editable SQL query which is built on top of existing tables and does not store any data itself, whilst a table is the original data storage object that can be used by a view.

Why use views instead of tables in SQL?

If you are wanting to add new data to your database then this must be done by adding it to a table, however if you are wanting to transform existing tabular data then you should be using a view to do this.

A situation where you would use a view rather than a table is when an end user requires a set of data to be made available for reporting. Very often the raw data will be accessible via an existing tabular data model, but not in the format required for the report. To make the reporting data available, a view would be created on top of the raw data which transforms it in the required format for reporting. This is preferable over creating a new table as you avoid copying the data and don’t need to regularly update the new table with fresh data.

Creating views and tables in SQL

Creating views and tables in SQL requires similar commands regardless of which technology you are using. In the following examples I will be showing how it can be done in Snowflake.

Create a view in Snowflake

The creation of a view is a straight forward process where you just need to define the select statement you require.

create or replace view <name> as <select_statement>

create or replace view cheap_products
as select * from products where price < 10

Create a table in Snowflake

There are several ways to create a table. You could either create a brand new table and load data into it afterwards, or create a table as a select statement which will load the resulting data from that select statement into a new table. I will show both examples here:

create or replace table <name> (<column_name> <data_type>)
create or replace table <name> as <select_statement>

create or replace table cheap_products (
	product_id number,
	price number)

create or replace table cheap_products as 
select * from products where price < 10

Which is faster, view or table?

If the view is querying a single table with a minor adjustment, such as a where clause or a calculation, then the speed difference between querying the table vs the view will be negligible. However, if the view joins multiple tables or has some major operations then querying the single table will be considerably faster.

View vs table, which is better?

Views and tables are different methods of organising data and are used for fundamentally different use cases. If you need to store data long term and use it as part of a data model then you should create a table. However, if you want to join multiple tables, for example as part of an analysis or reporting, then a view would be the better choice.

Related articles

Views

Snowflake materialized view
Materialized view vs table
View vs materialized view

Tables

Create table as select in Snowflake
Create a temp table in Snowflake

Functions

Select all columns except one or multiple

References

Snowflake table documentation
Snowflake view documentation
Star schema explanation