SQL Views (original) (raw)

Summary: in this tutorial, you’ll learn about SQL views and managing database views such as creating new views, modify existing views, and dropping views.

Introduction to SQL Views #

In SQL, a view is a named query stored in the database system.

Unlike a table, a view does not store data physically. The database system only stores the view’s definition.

When you query data from a view, the database system executes the query to retrieve data from the underlying tables.

Some views can be updatable. It means you can modify the underlying tables’ data via updatable views.

Creating SQL views #

To create a new view, you use the CREATE VIEW statement followed by a query as follows:

CREATE VIEW [IF NOT EXISTS] view_name AS queryCode language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the view after the CREATE VIEW clause. The IF NOT EXISTS option prevents creating a view that already exists.
• Second, provide a SELECT statement after the AS keyword. The SELECT statement is called a view defining-query.

The SELECT statement may retrieve data from one or more tables. These tables are known as the base tables.

For example, the following statement creates a view employee_contacts based on the employees and departments tables:

CREATE VIEW employee_contacts AS SELECT first_name, last_name, email, phone_number, department_name FROM employees e INNER JOIN departments d ON d.department_id = e.department_id ORDER BY first_name;Code language: SQL (Structured Query Language) (sql)

Try it

In this example, we create a view based on a query that joins data from the employees and departments tables.

Specifying SQL view columns #

By default, the view uses the column names of the defining query. If you want to use different names, you can explicitly specify them in the CREATE VIEW clause:

CREATE VIEW view_name(column1, column2, ...) AS query;Code language: SQL (Structured Query Language) (sql)

In this syntax, we specify a comma list of columns for the view after the view name

For example, the following statement creates a view called payroll with explicit columns that are different from the base tables:

CREATE VIEW payroll (first_name, last_name, job, compensation) AS SELECT first_name, last_name, job_title, salary FROM employees e INNER JOIN jobs j ON j.job_id = e.job_id ORDER BY first_name;Code language: SQL (Structured Query Language) (sql)

Try it

Querying data from views #

Querying data from views is the same as selecting data from tables. For example, the following statement selects data from the employee_contacts view.

SELECT * FROM employee_contacts;Code language: SQL (Structured Query Language) (sql)

Try it

Modifying Views #

To modify the view structure, such as adding new columns to the view or removing columns from a view, you use the CREATE OR REPLACE VIEW statement.

CREATE OR REPLACE view_name AS query;Code language: SQL (Structured Query Language) (sql)

The CREATE OR REPLACE statement creates a view if it does not exist or replaces the existing view.

For example, the following statement changes the payroll view by adding the department column and rename the compensation column to salary column:

CREATE OR REPLACE VIEW payroll (first_name, last_name, job, department, salary) AS SELECT first_name, last_name, job_title, department_name, salary FROM employees e INNER JOIN jobs j ON j.job_id = e.job_id INNER JOIN departments d ON d.department_id = e.department_id ORDER BY first_name;Code language: SQL (Structured Query Language) (sql)

Try it

SELECT * FROM payroll;Code language: SQL (Structured Query Language) (sql)

Try it

Removing an SQL View #

To remove a view from the database, you use the DROP VIEW statement with the following syntax:

DROP VIEW IF EXISTS view_name;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the view (view_name) you want to remove after the DROP VIEWkeywords.
• Second, use the IF EXISTS option to conditionally drop a view only if it exists. If you don’t use the IF EXISTS option, and the view does not exist; the database system will issue an error. The IF EXISTS option prevents the database from issuing an error from dropping a view that does not exist.

Note that the DROP VIEW statement deletes the view only, not the base tables.

The following statement uses the DROP VIEW statement to drop the payroll view from the database:

DROP VIEW payroll;Code language: SQL (Structured Query Language) (sql)

Try it

Why use SQL Views #

• Simplify complex queries: Views allow you to encapsulate complex queries with joins, aggregates, unions, and filters, helping you write more straightforward queries.
• Data securities: Views can limit access to sensitive data in the underlying tables by exposing only the relevant data. Rather than giving users access to database tables, you create a view that reveals only necessary data and grants them access to it.
• Consistent interfaces: Views provide a consistent data interface to client applications even if the underlying table structure changes.

Summary #

• A view is a named query stored in the database.
• Use the CREATE VIEW statement to create a new view.
• Use the CREATE OR REPLACE VIEW statement to create or modify a view.
• Use the DROP VIEW statement to drop a view.

Quiz #

Databases #

• PostgreSQL Views
• MySQL Views
• MariaDB Views
• SQLite Views
• Oracle Views
• Db2 Views