SQL guide · Tables, views & databases (DDL)

SQL CREATE VIEW: Save a Query as a Virtual Table

A view is a saved SELECT query that you can use like a table. When you run SELECT * FROM my_view, the database runs the stored query behind it and returns the result. A standard view stores no data of its own, so it always reflects the current contents of the underlying tables.

Views are useful for hiding complexity and for controlling access. A long join across customers, orders and products can be written once and then reused by analysts as a simple view. A view can also expose only certain columns or rows, so you can grant users access to the view without giving them the base table. Because the logic lives in one place, changing the view updates every query that uses it.

To change an existing view, many databases offer CREATE OR REPLACE VIEW, which creates the view if it is missing and replaces its definition if it exists. Where that is not available, you drop the view and create it again, or use ALTER VIEW. Simple views over a single table can often be updated through, but views with joins, aggregates or DISTINCT are usually read-only.

Basic syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name;

view_name follows the usual naming rules and shares a namespace with tables. The query after AS can use joins, filters, aggregates and other views. You can list column names after the view name, as in CREATE VIEW v (a, b) AS ..., to rename the output columns.

SQL CREATE VIEW examples

A simple filtered view

This view lists only active customers and hides the internal columns. Anyone querying it sees a clean table, and the filter is applied automatically.

CREATE VIEW active_customers AS
SELECT customer_id, name, email
FROM customers
WHERE status = 'active';

SELECT * FROM active_customers
WHERE email LIKE '%@example.com';

A view that joins several tables

Joining orders to customers and products is a task repeated in many reports. Saving it as a view lets people select from one object. Each row shows the order line with readable names.

CREATE VIEW order_details AS
SELECT o.order_id,
       o.order_date,
       c.name AS customer_name,
       p.product_name,
       oi.quantity,
       oi.quantity * oi.unit_price AS line_total
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id;

Replace an existing view definition

When requirements change, CREATE OR REPLACE VIEW updates the definition without dropping dependent objects or grants. In PostgreSQL and MySQL the new query must keep the existing columns in the same order and type, and can only add new ones at the end.

CREATE OR REPLACE VIEW active_customers AS
SELECT customer_id, name, email, created_at
FROM customers
WHERE status = 'active';

A summary view with aggregation

A view can hold a GROUP BY query so that monthly totals are always current. This one is read-only because it aggregates rows, but it is convenient for dashboards.

CREATE VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(total) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

Where SQL dialects differ

PostgreSQL: Supports CREATE OR REPLACE VIEW, CREATE TEMP VIEW, and materialized views through CREATE MATERIALIZED VIEW, which stores results and is refreshed with REFRESH MATERIALIZED VIEW. Simple views are automatically updatable.

MySQL: Supports CREATE OR REPLACE VIEW and ALTER VIEW. A view cannot contain a subquery in the FROM clause before MySQL 5.7.7, and it cannot reference temporary tables. Materialized views are not built in.

SQL Server: CREATE VIEW must be the first statement in a batch. CREATE OR ALTER VIEW is available from SQL Server 2016 SP1 and replaces the older pattern of dropping and recreating. Add WITH SCHEMABINDING to prevent changes to underlying tables and to allow indexed views.

SQLite: Supports CREATE VIEW [IF NOT EXISTS] and CREATE TEMP VIEW, but has no CREATE OR REPLACE VIEW. Drop the view first to change it. Views in SQLite are read-only unless you add INSTEAD OF triggers.

Oracle: Supports CREATE OR REPLACE VIEW, and options such as WITH READ ONLY and WITH CHECK OPTION. Materialized views are created with CREATE MATERIALIZED VIEW.

Common mistakes

  • Using SELECT * inside a view. If columns are later added to the table, some databases do not update the view, which can lead to missing or shifted columns. List the columns explicitly.
  • Assuming a view is faster than the query behind it. A normal view stores no results, so it costs the same as running the underlying query each time.
  • Adding ORDER BY in the view definition and expecting sorted output. Sort in the outer query instead, because the order is not guaranteed and SQL Server rejects it without TOP.
  • Trying to insert or update through a view that uses joins, aggregates or DISTINCT. These views are generally not updatable.
  • Stacking many views on top of each other. Deeply nested views are hard to debug and the optimiser may produce poor plans.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator