CREATE VIEW syntax in SQL

Introduction

A view lets you save a query as a virtual table. It helps you reuse complex queries easily without rewriting them.

You want to simplify repeated complex queries for easier use.

You want to show only specific columns or rows from a table to users.

You want to hide the complexity of joins or calculations from users.

You want to create a consistent way to look at data that might change.

You want to improve security by restricting access to certain data.

Syntax

SQL

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

The view_name is the name you give to your virtual table.

The SELECT statement defines what data the view will show.

Examples

This view shows only active customers with their id, name, and email.

SQL

CREATE VIEW active_customers AS
SELECT id, name, email
FROM customers
WHERE status = 'active';

This view lists product IDs and their prices from the products table.

SQL

CREATE VIEW product_prices AS
SELECT product_id, price
FROM products;

This view shows orders placed after January 1, 2024.

SQL

CREATE VIEW recent_orders AS
SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date > '2024-01-01';

Sample Program

This creates a view named high_salary_employees that shows employees earning more than 70000. Then it selects all rows from this view.

SQL

CREATE VIEW high_salary_employees AS
SELECT employee_id, name, salary
FROM employees
WHERE salary > 70000;

SELECT * FROM high_salary_employees;

Important Notes

Views do not store data themselves; they run the saved query each time you use them.

You can update data through views only if they meet certain rules (like no joins or aggregates).

Dropping a view does not affect the original tables.

Summary

CREATE VIEW saves a SELECT query as a virtual table.

Views simplify complex queries and improve data access control.

You use views by selecting from them like regular tables.