Database view explained

What is a database view?

In SQL, a database view, or simply view, is a searchable database object that doesn’t store data, but it aggregate data from one or more tables using a defined query.

To put it in a simple term, a view is a set of results from a query, which you save it under a name (view name) without having to create an actual table. And later you can query data from view just like how you treat a normal table of data.

What is a view for?

A view is created to serve these purposes:

Simplify the query and reduce the work: A view helps contain aggregated data from multiple tables using the JOINs statements, or a subset of data from a very complex query. So the next time you need that result, you can just use the SELECT statement instead of typing the complex query again.
Increase security: You might limit the access to sensitive data by showing a view with the only necessary information and no more.
Enable backward compatibility: You can make changes to a current table and pretend not much changed by having a view to being referenced by the system.

CREATE VIEW syntax

Here is the generic syntax to create database view:

CREATE VIEW view_name AS
SELECT column_names
FROM table_name
WHERE conditions;

Example

We have table departments:

dept_no	dept_name
d001	Marketing
d002	Finance
d003	Human Resources
d004	Production
d005	Development
d006	Quality Management
d007	Sales
d008	Research
d009	Customer Service

Now create a view top_3_departments that shows the top 3 rows from the table departments:

CREATE VIEW top_3_departments AS
  SELECT *
  FROM departments
  LIMIT 3;

The view is created, from now on, you can query data from the view top_3_departments just like a normal table of data:

SELECT * FROM top_3_departments;

The result is:

dept_no	dept_name
d009	Customer Service
d005	Development
d002	Finance

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.