SQL CREATE VIEW

What is SQL CREATE VIEW?

CREATE VIEW defines a named, reusable query whose result set behaves like a virtual table.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CREATE VIEW: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift, DB2

SQL CREATE VIEW Full Explanation

CREATE VIEW stores a SELECT statement under a view name so that users can query the view as if it were a table. The view does not hold data itself; it dynamically returns rows produced by the underlying query each time it is referenced. Views encapsulate complex joins or calculations, enforce security by exposing only specific columns, and provide a stable interface to evolving schemas. Some dialects allow updatable views, with restrictions, and support modifiers like OR REPLACE, IF NOT EXISTS, WITH CHECK OPTION, or SECURITY BARER. Because a view depends on base tables, altering or dropping those tables can invalidate the view. Performance depends on the optimizer; most engines rewrite a view reference into the underlying query while others can cache results or support materialized views for persistence.

SQL CREATE VIEW Syntax

CREATE [OR REPLACE] VIEW view_name
    [ (column1, column2, ...) ]
AS
    select_statement
    [ WITH CHECK OPTION ];

SQL CREATE VIEW Parameters

view_name (identifier) - Name of the view to create.
column_list (optional list) - Overrides column names returned by the query.
select_statement (query) - Any valid SELECT statement.
OR REPLACE (keyword) - Replaces an existing view of the same name.
WITH CHECK OPTION (keyword) - Prevents inserts or updates through the view that violate its predicate.
IF NOT EXISTS – keyword (varies) - Skip creation if the view already exists.

Example Queries Using SQL CREATE VIEW

-- Simple view for active users
CREATE VIEW active_users AS
SELECT id, email FROM users WHERE status = 'active';

-- Replace an existing view
CREATE OR REPLACE VIEW revenue_by_month AS
SELECT date_trunc('month', paid_at) AS month,
       SUM(amount) AS total_revenue
FROM payments
GROUP BY month;

-- Updatable view with check option
CREATE VIEW sales_eu AS
SELECT * FROM sales WHERE region = 'EU'
WITH CHECK OPTION;

Expected Output Using SQL CREATE VIEW

Each statement registers the view definition in the database catalog
No rows are returned at creation time
Subsequent SELECTs against the view return the query results defined in AS clause

Use Cases with SQL CREATE VIEW

Simplify repeated complex joins or aggregations
Expose a restricted subset of columns or rows for security
Provide backward compatibility when base tables change
Support logical data modeling such as dimensional or reporting layers
Enable updatable subset views with WITH CHECK OPTION

Common Mistakes with SQL CREATE VIEW

Assuming a view stores data permanently like a table
Forgetting to include OR REPLACE when modifying a view definition
Using SELECT * inside views then breaking downstream code when table columns change
Relying on performance without indexing base tables or considering materialized views
Dropping or altering base tables without checking dependent views

First Introduced In

SQL-92 standard

Frequently Asked Questions

What is the difference between a view and a table?

A table physically stores data on disk. A view stores only the SELECT definition and returns data dynamically from underlying tables.

Can I index a view?

Standard views cannot be indexed directly. Some databases allow indexed or materialized views which persist and index their data.

How do I drop a view?

Run `DROP VIEW view_name;` Use `IF EXISTS` in dialects that support it to avoid errors if the view is missing.

Are views slower than querying the base tables?

Performance is usually similar because the optimizer expands the view into the underlying query. For heavy aggregations or joins, consider materialized views or additional indexing.