Views v15
Consider the following SELECT command:
SELECT ename, sal, sal * 24 AS yearly_salary, deptno FROM emp;
Output
ename | sal | yearly_salary | deptno --------+---------+---------------+-------- SMITH | 800.00 | 19200.00 | 20 ALLEN | 1600.00 | 38400.00 | 30 WARD | 1250.00 | 30000.00 | 30 JONES | 2975.00 | 71400.00 | 20 MARTIN | 1250.00 | 30000.00 | 30 BLAKE | 2850.00 | 68400.00 | 30 CLARK | 2450.00 | 58800.00 | 10 SCOTT | 3000.00 | 72000.00 | 20 KING | 5000.00 | 120000.00 | 10 TURNER | 1500.00 | 36000.00 | 30 ADAMS | 1100.00 | 26400.00 | 20 JAMES | 950.00 | 22800.00 | 30 FORD | 3000.00 | 72000.00 | 20 MILLER | 1300.00 | 31200.00 | 10 (14 rows)
If this is a query that you use repeatedly, a shorthand method of reusing this query without retyping the entire SELECT command each time is to create a view:
CREATE VIEW employee_pay AS SELECT ename, sal, sal * 24 AS yearly_salary, deptno FROM emp;
The view name, employee_pay, can now be used like an ordinary table name to perform the query:
SELECT * FROM employee_pay;
Output
ename | sal | yearly_salary | deptno --------+---------+---------------+-------- SMITH | 800.00 | 19200.00 | 20 ALLEN | 1600.00 | 38400.00 | 30 WARD | 1250.00 | 30000.00 | 30 JONES | 2975.00 | 71400.00 | 20 MARTIN | 1250.00 | 30000.00 | 30 BLAKE | 2850.00 | 68400.00 | 30 CLARK | 2450.00 | 58800.00 | 10 SCOTT | 3000.00 | 72000.00 | 20 KING | 5000.00 | 120000.00 | 10 TURNER | 1500.00 | 36000.00 | 30 ADAMS | 1100.00 | 26400.00 | 20 JAMES | 950.00 | 22800.00 | 30 FORD | 3000.00 | 72000.00 | 20 MILLER | 1300.00 | 31200.00 | 10 (14 rows)
Making liberal use of views is a key aspect of good SQL database design. Views provide a consistent interface that encapsulate details of the structure of your tables that might change as your application evolves.
You can use views in almost any place you can use a real table. Building views on other views is common.