Views v12
Consider the following SELECT command.
SELECT ename, sal, sal * 24 AS yearly_salary, deptno FROM emp; 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 is used repeatedly, a shorthand method of reusing this query without re-typing the entire SELECT command each time is to create a view as shown below.
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; 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 which may change as your application evolves.
Views can be used in almost any place a real table can be used. Building views upon other views is not uncommon.