Query view in MySQL

22.Jul.2010

Since MySQL 5, query view is an option we can use to create a virtual table based on the result-set of an SQL statement. View contains rows and columns, as if it was a real table. The fields in a view are fields from one or more tables in the database.

Query view is used when your query upon couple of databases is too complex and you would simplify your own query. In MySQL, you can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table, but you can not do INSERT or UPDATE. MySQL only supports read-only views.

To create a simple query view, use following snippet as an example:

1
2
3
4
5
CREATE VIEW query_view AS
SELECT COLUMNS
FROM TABLE
JOIN table2 ON TABLE.prop1=table2.prop1
WHERE conditions

You can as well update query view:

1
2
3
4
5
CREATE OR REPLACE VIEW query_view AS
SELECT COLUMNS
FROM TABLE
JOIN table2 ON TABLE.prop1=table2.prop1
WHERE conditions

MySQL still doesn’t support materialized views so these views are not as performant as they could be.