A view is a virtual table based on the result of an SQL query.
View types
-
read-only view - for query purposes.
-
updatable views - for query and data management (INSERT, UPDATE, DELETE operations) purposes. There are some restrictions for this type of views such as only one FROM clause or no aggregate functions. It may be not supported by all DBMS.
-
materialized views - static snapshot of data while accessing the view to improve the querying performance. The query related to such view is executed only after view's creation and on demand. It can be also indexed as the regular data tables to achieve even better performance. It may be not supported by all DBMS (for example: MySQL).
CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example:
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);
CREATE VIEW customers_providers(
id, element_id, element_type, code
) AS
SELECT CONCAT_WS('_', 'Customer', id) AS id, id AS element_id, 'Customer' AS element_type, code
FROM customers
UNION
SELECT CONCAT_WS('_', 'Provider', id) AS id, id AS element_id, 'Provider' AS element_type, code
FROM providers