SQL CREATE VIEW Statement

---

---

SQL CREATE VIEW Statement

The CREATE VIEW statement in SQL is used to create a virtual table based on the result of a SELECT query. A view does not store data physically but acts like a table that can be queried like any regular table.

• A view is a named SQL query stored in the database.
• Views can include joins, filtering conditions, grouping, and aggregation.
• You can select data from a view just like a table using SELECT statement.
• Views do not store data, they display data from the underlying base tables.

Syntax

Following is the basic syntax of the CREATE VIEW statement in SQL:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example: Create a Simple View

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement using the following query:

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, let us insert few records into this table using the INSERT statement as follows:

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

The table will be created as follows:

Now, we create a view that displays only the ID, NAME, and SALARY of customers from the CUSTOMERS table:

CREATE VIEW CUSTOMER_SALARY_VIEW AS
SELECT ID, NAME, SALARY
FROM CUSTOMERS;

We get the following output:

Query OK, 0 rows affected (0.05 sec)

Verification

You can retrieve data from the view using the following SELECT query:

SELECT * FROM CUSTOMER_SALARY_VIEW;

Following is the table produced:

SQL View with WHERE Clause

You can also create SQL views that include a WHERE clause to filter the data shown through the view. This allows you to present only a subset of rows from the base table(s), based on specific conditions.

Syntax

Following is the basic syntax to create views using the WHERE clause in SQL:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example

In this example, we create a view that only shows customers with a salary greater than 2000:

CREATE VIEW HIGH_EARNERS AS
SELECT ID, NAME, SALARY
FROM CUSTOMERS
WHERE SALARY > 2000;

We get the following output:

Query OK, 0 rows affected (0.05 sec)

Verification

You can use the following SELECT query to check the view:

SELECT * FROM HIGH_EARNERS;

The table produced is as shown below:

Using Joins in Views

You can create views using JOIN operations to combine and present data from multiple related tables as a single virtual table.

The view can include selected columns from each table and can even apply filters using a WHERE clause.

Example

Assume you have another table named ORDERS having the following structure:

Now, we have two tables: ORDERS and CUSTOMERS. We can create a view to show customer names along with their order IDs:

CREATE VIEW CUSTOMER_ORDERS_VIEW AS
SELECT C.ID, C.NAME, O.ORDER_ID
FROM CUSTOMERS C
JOIN ORDERS O ON C.ID = O.CUSTOMER_ID;

We get the following output:

Query OK, 0 rows affected (0.05 sec)

Verification

We can view the CUSTOMER_ORDERS_VIEW table using the following SELECT query:

SELECT * FROM CUSTOMER_ORDERS_VIEW;

Following is the table produced:

Modifying Views in SQL

You can change the definition of an existing view using the CREATE OR REPLACE VIEW statement. This is useful when you want to update the columns or logic in the view without dropping and recreating it manually.

Syntax

Following is the basic syntax to modify a view in SQL:

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example

Let us modify the existing view CUSTOMER_SALARY_VIEW to include only specific columns from the CUSTOMERS table:

CREATE OR REPLACE VIEW CUSTOMER_SALARY_VIEW AS
SELECT ID, NAME, AGE, SALARY
FROM CUSTOMERS;

Following is the output obtained:

Query OK, 0 rows affected (0.04 sec)

Verification

To verify that the view has been updated, you can query it like a regular table:

SELECT * FROM CUSTOMER_SALARY_VIEW;

Following is the table obtained:

Dropping a View in SQL

If a view is no longer needed, you can remove it using the DROP VIEW statement.

Syntax

Following is the basic syntax to drop a view in SQL:

DROP VIEW view_name;

Example

In the example below, we drop a view named CUSTOMER_SALARY_VIEW:

DROP VIEW CUSTOMER_SALARY_VIEW;

Following is the output obtained:

Query OK, 0 rows affected (0.04 sec)

Verification

After dropping the view, trying to query it will result in an error:

SELECT * FROM CUSTOMER_SALARY_VIEW;

It will show the following error:

ERROR 1146 (42S02): Table 'testdb.customer_salary_view' doesn't exist

Important Points About CREATE VIEW

Following are the important points to remember when creating views in SQL:

• Views do not store data, they display data from underlying tables in real-time.
• You can query, filter, and join views just like normal tables.
• Use CREATE OR REPLACE VIEW to update a view.
• To remove a view, use DROP VIEW.
• Views are supported in most relational database systems including MySQL, PostgreSQL, Oracle, and SQL Server.