CREATE VIEW

The Syntax In Depth

The basic form:

sql
CREATE VIEW view_name [(column_alias1, column_alias2, ...)] AS
<select_statement>
[WITH [CASCADED | LOCAL] CHECK OPTION];

Example with explicit column aliases:

sql
CREATE VIEW dept_headcount (dept_name, num_employees) AS
SELECT d.name, COUNT(e.id)
FROM departments d
LEFT JOIN employees e ON e.department = d.id
GROUP BY d.name;

The optional column-alias list lets you rename output columns at the view level, independent of the underlying SELECT's own aliases — useful when the query's expressions don't naturally produce clean names (e.g., COUNT(e.id) becomes num_employees).

CREATE OR REPLACE VIEW

Dropping and recreating a view is often undesirable because it can wipe out permissions (GRANTs) that were attached to it. Most engines (PostgreSQL, MySQL, Oracle) support an in-place redefinition:

sql
CREATE OR REPLACE VIEW high_earners AS
SELECT id, name, salary, department
FROM employees
WHERE salary > 85000;

This updates the view's stored query definition without dropping the view object itself — existing grants, and (in most engines) the view's identity for dependent views, remain intact. There are limits: some engines disallow changing the number or order of output columns with CREATE OR REPLACE (you'd need to DROP and recreate, or add new columns only at the end).

DROP VIEW

sql
DROP VIEW high_earners;
DROP VIEW IF EXISTS high_earners;          -- safe if it may not exist
DROP VIEW high_earners CASCADE;            -- also drops dependent views (PostgreSQL)

Dropping a view removes only the stored query definition — the underlying base tables and their data are completely untouched, since the view never held any data to begin with.

The View Stores a Definition, Not Data

This is the single most important fact about views: CREATE VIEW does not run the SELECT and save its output. It parses and stores the query text/plan, catalogs it in the system tables (e.g., information_schema.views in most SQL engines), and re-executes that query every single time the view is referenced — always reflecting the current state of the base tables. Compare:

sql
-- Ordinary view: always current, always re-computed
CREATE VIEW high_earners AS SELECT id, name, salary FROM employees WHERE salary > 80000;

-- Materialized view: computed once, stored physically, goes stale until refreshed
CREATE MATERIALIZED VIEW high_earners_mv AS SELECT id, name, salary FROM employees WHERE salary > 80000;

See 19.6 for the full materialized-view trade-off.

Edge Cases

• Forward references: a view's SELECT can reference another view, but not a table/view that doesn't exist yet — dependency order matters at creation time.
• `CREATE OR REPLACE VIEW` and column count: in PostgreSQL, you cannot remove or reorder existing output columns this way — only append new ones at the end or change their defining expressions; violating this raises an error, forcing a DROP/CREATE.
• Dropping a view others depend on: DROP VIEW fails by default (in engines with RESTRICT semantics) if other views select from it, unless CASCADE is used.
• Naming/schema: a view lives in a schema just like a table and is subject to the same uniqueness rule.

Key Takeaways / Interview Q&A

Q: What's the difference between DROP VIEW and CREATE OR REPLACE VIEW when you need to change a view's definition? A: DROP + CREATE destroys the view object (and often its grants) before recreating it; CREATE OR REPLACE VIEW updates the definition of the existing object in place, generally preserving permissions.

Q: Does DROP VIEW delete any data? A: No — it only removes the stored query definition; the base tables are unaffected.

Q: If I `CREATE OR REPLACE VIEW v AS SELECT id, name FROM employees;` and v was previously `SELECT id, name, salary FROM employees;`, does that work? A: In most engines, no — removing an existing output column via replace is rejected; you'd need to DROP and recreate the view instead.