Creating a SQL View in Standard SQL

This lesson uses standard, vendor-neutral SQL rather than Oracle-specific syntax, since the underlying mechanics of creating a view are the same across virtually every relational database, and the ANSI/ISO SQL standard defines exactly how a view is meant to work regardless of which engine implements it. Everything covered here still applies directly to Oracle; it just describes the shared foundation Oracle builds its own specific syntax on top of, rather than Oracle's particular flavor of it.

ANSI SQL:2023 defines view creation as a single CREATE VIEW statement. There's no multi-step process built into the language itself, just a name, a query, and a handful of optional clauses:

CREATE VIEW MemberNameEmail AS
SELECT LastName, FirstName, Email, DateOfJoining
FROM MemberDetails;

Executing this doesn't retrieve or display any data itself; it causes the database to store the query under the name MemberNameEmail. Querying MemberNameEmail afterward is what actually runs the stored query and returns rows.

One habit worth carrying over regardless of how simple the syntax is: write and run the SELECT on its own first, confirm it returns exactly the rows and columns intended, joins, WHERE clauses, and all, before wrapping it in CREATE VIEW. That's the same "verify the piece independently before combining it" discipline already established for subqueries earlier in this course. It's a testing habit, not a requirement the language imposes.

A slightly larger example makes the same point with a query that isn't just a bare column list:

CREATE VIEW HighValueMembers AS
SELECT LastName, FirstName, Email
FROM MemberDetails
WHERE DateOfJoining < DATE '2020-01-01';

Whether the query behind a view is a two-column projection or a filtered, multi-condition statement like this one, the CREATE VIEW wrapper works identically. The complexity lives entirely in the query; the syntax that turns it into a named view never changes.

A view is often called a virtual table, since it presents rows and columns exactly like a table would, but its data set isn't physically stored anywhere. The view's result doesn't exist until the moment it's queried; at that point, the database runs the stored query against the current base tables and presents whatever that query returns. To whoever queries it, a view looks indistinguishable from a table, columns, rows, the works, but nothing about it is persisted between queries. A view can be as narrow as a handful of columns from one table, or as elaborate as a query joining most of the tables in a database together.

This has a direct practical consequence worth stating plainly: creating a view costs almost nothing in storage, and it never duplicates the base table's data. HighValueMembers, once created, doesn't hold a copy of any MemberDetails rows; it holds only the text of the query that defines it. Every byte of actual data still lives in MemberDetails alone, queried fresh each time HighValueMembers is referenced.

The minimal CREATE VIEW shown above leaves out several optional pieces the SQL:2023 standard defines. The full form looks like this:

CREATE [ RECURSIVE ] VIEW [schema.]view_name
    [ (column_name [, column_name ...]) ]
AS
    query_expression
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ];

Piece	Required?	What it does
CREATE VIEW	Yes	Defines a viewed table, not a stored set of rows.
RECURSIVE	No	Allows the view's own query to reference the view itself. Requires the column list.
schema.view_name	Name yes, schema optional	An unqualified name goes into the current default schema.
(column_name, ...)	No, usually	Explicit column names for the view. Required if the query doesn't already produce clear, usable names, and always required for RECURSIVE.
AS query_expression	Yes	Any valid query, a plain SELECT, a UNION, or similar. This is the view.
WITH CHECK OPTION	No	For an updatable view: rejects any INSERT or UPDATE through the view that would produce a row the view's own conditions wouldn't select.
CASCADED / LOCAL	No	Controls how CHECK OPTION behaves when this view is itself built on another view. CASCADED is the default if WITH CHECK OPTION is written without specifying either.

Naming columns explicitly and adding a check option produces a more complete definition than the bare minimum shown earlier:

CREATE VIEW active_customers (customer_id, customer_name)
AS
    SELECT customer_id, name
    FROM customers
    WHERE status = 'ACTIVE'
WITH CASCADED CHECK OPTION;

RECURSIVE deserves a word of its own, since it's the one piece of this syntax genuinely unlike everything else covered in this course so far. A recursive view can reference its own name inside its defining query, which makes it suited to hierarchical data an ordinary view can't express cleanly, an employee-and-manager reporting chain, a bill-of-materials breakdown, or any structure where a row's relationship to another row of the same shape needs to be walked repeatedly rather than joined once. It's a genuinely specialized tool, used rarely compared to the plain views covered throughout this module, and it's mentioned here for completeness rather than as something to build fluency with immediately.

The SQL standard allows some views to be used in INSERT, UPDATE, and DELETE, but only when the underlying query is simple enough: generally a single table, no aggregate functions, and no DISTINCT, GROUP BY, or HAVING collapsing multiple rows into one. WITH CHECK OPTION only makes sense on a view that's already updatable in the first place; adding it to a view built on a GROUP BY wouldn't have anything meaningful to enforce, since that kind of view was never eligible for INSERT or UPDATE to begin with.

HighValueMembers from earlier qualifies as updatable under this rule: one table, no aggregation, nothing collapsing rows together. An UPDATE through it is legal:

UPDATE HighValueMembers
SET Email = 'newaddress@example.com'
WHERE LastName = 'Whitfield';

A view built around an aggregate function doesn't qualify, and for a straightforward reason: there's no single underlying row to update. Consider a view summarizing membership counts by join year:

CREATE VIEW MembersByYear AS
SELECT EXTRACT(YEAR FROM DateOfJoining) AS JoinYear, COUNT(*) AS MemberCount
FROM MemberDetails
GROUP BY EXTRACT(YEAR FROM DateOfJoining);

An attempt to UPDATE MembersByYear SET MemberCount = 50 WHERE JoinYear = 2023 has no sensible destination; MemberCount isn't a column stored anywhere, it's a computed total across however many rows happened to share that join year. The standard's restriction here isn't arbitrary; it reflects the fact that a write against a summarized value has no unambiguous underlying row to actually change.

A few commonly used pieces of view syntax aren't part of core SQL:2023 at all; they're vendor-specific extensions layered on top of the standard by individual database products:

• OR REPLACE and IF NOT EXISTS
• Temporary views
• Encryption, schema-binding, algorithm, or security-definer clauses
• Materialized views, which are a separate, non-core construct in every product that offers them

This is worth knowing specifically because Oracle's CREATE OR REPLACE VIEW, used throughout this course, is exactly this kind of vendor convenience: genuinely useful, but an Oracle-specific addition on top of the ANSI baseline, not something the standard itself defines. Changing a view after creation, in the pure standard, means DROP VIEW followed by a fresh CREATE VIEW, or an implementation-specific ALTER VIEW where a given product supports one.

Every piece of Oracle-specific view syntax covered earlier in this course turns out to be a specific implementation of one of these standard-defined concepts, not something unrelated to it. WITH CHECK OPTION, used with Oracle's own CONSTRAINT constraint_name naming syntax, is the exact same standard clause described in this lesson's syntax table, just with Oracle's optional naming convention layered on top. Oracle's WITH READ ONLY clause, meanwhile, isn't part of the ANSI standard shown here at all; it's a separate Oracle-specific way of declaring a view non-updatable outright, distinct from simply having a query shape the standard wouldn't consider updatable in the first place.

The updatability rule covered above, one table, no aggregation, no row-collapsing constructs, is also the same underlying principle behind why Oracle allows INSERT and UPDATE against a simple, single-table view but not against a GROUP BY-based view like the department revenue examples used elsewhere in this course. Oracle's specific column-level requirements for updatable views, covered in an earlier lesson, are Oracle's own implementation details on top of this same standard-defined foundation; the standard says roughly what kinds of views qualify, and each vendor fills in the specific mechanics of enforcing it.

The precise tables making up a data dictionary vary somewhat between database products, but seeing one typical example makes the concept concrete. A common pattern is a backbone table that documents every other data dictionary table, alongside separate tables tracking base tables, their columns, their indexes, and their foreign keys. A syscolumn-style table, for instance, describes the columns belonging to each table, including the data dictionary's own tables:

Every product organizes these details somewhat differently, which is exactly why the SQL standard also defines a portable alternative that doesn't depend on any one vendor's internal table names: INFORMATION_SCHEMA.VIEWS. Querying it is the standard-defined way to inspect a view's definition regardless of which product is running underneath:

SELECT view_name, view_definition
FROM INFORMATION_SCHEMA.VIEWS
WHERE table_schema = 'your_schema';

Where a vendor-specific syscolumn-style table reflects one product's particular internal design, INFORMATION_SCHEMA is part of the standard itself, meant to look and behave the same way across compliant implementations. A tool built to query INFORMATION_SCHEMA.VIEWS should work with minimal changes across any standard-compliant database; a tool built around a specific vendor's syscolumn layout works only against that one product, and needs to be rewritten entirely to run anywhere else. That portability is the entire reason the standard bothered to define INFORMATION_SCHEMA at all, rather than simply leaving data dictionary structure as an unaddressed implementation detail.

Looking Ahead

Creating a view, at the level of pure SQL, is one statement: CREATE VIEW, a name, and a query. Everything else in this lesson, the optional column list and check option, updatability rules, RECURSIVE, is refinement around that one core statement, not a replacement for it.

A few points worth carrying forward:

• CREATE VIEW is a single statement in the SQL standard; there's no multi-step process the language itself requires, though building and testing the underlying query independently first is still a sound habit.
• The full syntax adds an optional column list, an optional check option with CASCADED or LOCAL behavior, and RECURSIVE for views that reference themselves, none of which are required for a basic view.
• Updatability comes down to one core rule: a single table, no aggregation, nothing collapsing multiple rows into one. A view failing that test simply has no unambiguous row to write back to.
• OR REPLACE, IF NOT EXISTS, temporary views, and materialized views are all vendor extensions layered on top of the ANSI baseline, not part of the core standard, which is worth remembering the next time CREATE OR REPLACE VIEW shows up in an Oracle-specific lesson.
• INFORMATION_SCHEMA.VIEWS is the standard's own portable way to inspect a view's definition, independent of whatever vendor-specific data dictionary structure a given product uses internally.

The vocabulary in this lesson, updatable views, check options, view definitions stored as query text, is the same vocabulary used throughout the rest of this course; only the specific syntax realizing it changes from one product to the next.