Views in SQL

What is a View

TLDR: A view is like a query you can save for later.

A view is defined as a mechanism for querying data. I think of it as a way to create a query and save it for later. By creating a view you have access to a specific set of data without creating a new table or taking up space.

How Can I create a View

A view can be created by assigning a name to a select statement and then storing the query for others to use. (Similar to how we can store a value in a variable to use later). Then, other people can use your view to access data as if they were querying an actual table. Sometimes you don't even know you're using a view.

CREATE VIEW customer_vw 
AS 
SELECT 
customer_id, 
first_name name,
last_name surname, 
concat(substr(email, 1, 2), '*****', substr(email, -4)) email 
FROM customer; 

The first part of the statement gives the view a name customer_vw. The second part of the statement is a select statement which must contain one expression for each column in the view.
Once this view is created it can be queried just like a table!

We can also use desc customer_vw to examine the fields and types that make up the data in the view.

You can join views to other tables too.

Why Use Views

1. Data Security - you can use views to mask data you don't want others to see
2. Data aggregation - you can use views to preaggregate data so that when others query the view they are querying data that may be more meaningful than just raw data from the database.
3. Hiding Complexity