SQL VIEW – Create, Replace, Modify & Drop

We can use SQL views for many reasons and are very powerful when used properly. Views allow us to store data into virtualized tables as a result of a user generated SQL query. We use DDL functionality to create, modify, or remove a view using SQL.

Related Article: 5 Reasons Why We Should Use SQL Views

ANSI-Standard SQL Syntax

CREATE VIEW <view_name> AS <sql_query>; — Create basic view in SQL

We can easily create a view using any standard SQL query. Depending on the database, parentheses may or may not be required around the query itself.

CREATE OR REPLACE VIEW <view_name> AS <sql_query>; — Create or override existing view

If we’re looking to modify our query we can add the OR REPLACE syntax to our query. As a result, if the query already exists it will be over-written. Note that not all databases support the OR REPLACE syntax (such as SQLite). As an alternative we can drop and recreate a new view.

CREATE VIEW IF NOT EXISTS <view_name> AS <sql_query>; — Create view only if does not exist

As a safety precaution we can add the IF NOT EXISTS syntax to be careful as to not override anything we have already created. Again, this is not supported by all databases so check your database documentation if further clarification is needed.

DROP VIEW <view_name>; — Delete or remove view from database

Using the DROP command allows us to remove any existing view from our database. We can always save off our SQL query if needed for future use.

Create View Practical Examples

In our following example we want to have a slice of our customer table with only our customers from Brazil.

Since we want to verify our results we can select from our view for validation.

We can also drop the view if we don’t have any use for it any further.