Create / modify a view

Oracle

CREATE OR REPLACE VIEW active_customer AS
SELECT id, name, email FROM customer WHERE status = 'ACTIVE';

-- Drop
DROP VIEW active_customer;

CREATE OR REPLACE VIEW both creates and modifies. For views that are updatable under certain conditions, WITH CHECK OPTION can be added.

PostgreSQL

CREATE OR REPLACE VIEW active_customer AS
SELECT id, name, email FROM customer WHERE status = 'ACTIVE';

DROP VIEW active_customer;

With CREATE OR REPLACE, new columns can only be added at the end; changing a column's type/order requires DROP first. Simple views are automatically updatable.

MySQL

CREATE OR REPLACE VIEW active_customer AS
SELECT id, name, email FROM customer WHERE status = 'ACTIVE';

-- Modify
ALTER VIEW active_customer AS SELECT id, name FROM customer;
DROP VIEW active_customer;

The definition is updated with CREATE OR REPLACE VIEW or ALTER VIEW. The execution method can be set with ALGORITHM = MERGE/TEMPTABLE; WITH CHECK OPTION protects updatable views.

SQL Server

CREATE OR ALTER VIEW active_customer AS
SELECT id, name, email FROM customer WHERE status = 'ACTIVE';

DROP VIEW active_customer;

CREATE OR ALTER VIEW (2016 SP1+) creates/modifies in a single statement. In older versions, use CREATE first, then ALTER VIEW. The view statement must be the first statement in the batch.

MongoDB

// Read-only view (on top of an aggregation pipeline)
db.createView("active_customer", "customer", [ { $match: { status: "ACTIVE" } }, { $project: { name: 1, email: 1 } } ]);

db.active_customer.drop();

db.createView creates a read-only view over a source collection and an aggregation pipeline. Views cannot be written to; to change the definition, drop and recreate it (or use collMod).

Redis

# Not applicable: Redis has no view concept; derived views are provided on the application
# side or with RedisJSON/RediSearch indexes.

There is no support for views defined over queries. For a similar need, keep precomputed keys or build a searchable index with RediSearch.

Elasticsearch

# Filtered logical view with an alias
POST _aliases
{ "actions": [ { "add": { "index": "customer", "alias": "active_customer",
  "filter": { "term": { "status": "ACTIVE" } } } } ] }

There is no classic SQL view; a filtered alias is the closest equivalent and applies the filter to queries automatically. For column selection, use _source filtering in the query.