SQL CREATE VIEW to create Views (Virtual Tables)

As your database grows, you will find yourself writing the same complex queries over and over: joining multiple tables, applying filters, computing aggregations, formatting output. Instead of duplicating that logic every time, SQL lets you save a query as a view, a virtual table that you can query just like a real table but that stores no data of its own.

This guide covers everything you need to know about SQL CREATE VIEW: how to create views, how to query them, when and how views can be updated, how to modify existing views with CREATE OR REPLACE VIEW, and how to drop views you no longer need. Every concept is paired with practical examples and clear outputs so you can start using views immediately.

What Is a View?

A view is a named SQL query stored in the database. When you query a view, the database executes the underlying query in real time and returns the results as if they came from a regular table. The view itself does not store any data. It is simply a saved definition of a SELECT statement.

Think of it like a saved filter on a spreadsheet. The underlying data lives in the original sheet. The filter just defines which rows and columns to show, and it always reflects the latest data.

Why Use Views?

• Simplify complex queries: Write a complicated join once, then SELECT * FROM my_view forever
• Encapsulate business logic: Define "active customers" or "monthly revenue" in one place
• Improve security: Expose only specific columns or rows to certain users
• Ensure consistency: Everyone queries the same definition instead of each person writing their own version
• Maintain backward compatibility: If a table structure changes, update the view definition and downstream queries keep working

The Sample Data

We will use the following tables throughout this guide:

departments table:

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    budget DECIMAL(12,2) NOT NULL
);
INSERT INTO departments (id, name, budget) VALUES
(1, 'Engineering', 500000),
(2, 'Sales',       300000),
(3, 'Marketing',   250000);

employees table:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department_id INT NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    hire_date DATE NOT NULL,
    is_active BIT NOT NULL,   -- use BOOLEAN in PostgreSQL
    CONSTRAINT fk_department
        FOREIGN KEY (department_id)
        REFERENCES departments(id)
);
INSERT INTO employees (id, name, department_id, salary, hire_date, is_active) VALUES
(1, 'Alice',   1, 95000, '2020-03-15', 1),
(2, 'Bob',     1, 88000, '2021-06-01', 1),
(3, 'Charlie', 2, 72000, '2019-11-20', 1),
(4, 'Diana',   2, 68000, '2022-01-10', 0),
(5, 'Eve',     3, 74000, '2020-08-05', 1),
(6, 'Frank',   3, 70000, '2023-02-14', 1);

orders table:

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    status VARCHAR(20) NOT NULL
);
INSERT INTO orders (id, customer_name, order_date, amount, status) VALUES
(1, 'Acme Corp',  '2024-01-05', 2500, 'completed'),
(2, 'Globex Inc', '2024-01-12', 4300, 'completed'),
(3, 'Acme Corp',  '2024-02-08', 1800, 'pending'),
(4, 'Initech',    '2024-02-15', 5200, 'completed'),
(5, 'Globex Inc', '2024-03-01', 3100, 'cancelled'),
(6, 'Acme Corp',  '2024-03-10', 2750, 'completed');

CREATE VIEW

The CREATE VIEW statement saves a SELECT query as a named virtual table.

Syntax

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

Your First View

Let's create a view that shows only active employees with their department names:

CREATE VIEW active_employees AS
SELECT
    e.id,
    e.name,
    d.name AS department,
    e.salary,
    e.hire_date
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.is_active = TRUE;

This view encapsulates the join between employees and departments along with the active filter. The view is now stored in the database and can be used like any table.

View with Computed Columns

Views can include expressions, calculations, and formatting:

CREATE VIEW employee_summary AS
SELECT
    e.id,
    e.name,
    d.name AS department,
    e.salary,
    ROUND(e.salary / 12, 2) AS monthly_salary,
    TIMESTAMPDIFF(YEAR, e.hire_date, CURDATE()) AS years_employed,
    CASE
        WHEN e.salary >= 90000 THEN 'Senior'
        WHEN e.salary >= 70000 THEN 'Mid-Level'
        ELSE 'Junior'
    END AS salary_band
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.is_active = TRUE;

Now the classification logic, monthly salary calculation, and tenure computation are all defined once and available to everyone who queries this view.

View with Aggregation

Views work perfectly with GROUP BY and aggregate functions:

CREATE VIEW department_stats AS
SELECT
    d.id AS department_id,
    d.name AS department_name,
    d.budget,
    COUNT(e.id) AS headcount,
    ROUND(AVG(e.salary), 2) AS avg_salary,
    COALESCE(SUM(e.salary), 0) AS total_salary,
    d.budget - COALESCE(SUM(e.salary), 0) AS remaining_budget
FROM departments d
LEFT JOIN employees e 
       ON d.id = e.department_id 
      AND e.is_active = TRUE
GROUP BY d.id, d.name, d.budget;

This creates a reusable department-level report that always reflects the latest data.

Querying a View

Once created, a view is queried exactly like a regular table. You can use SELECT, WHERE, ORDER BY, JOIN, subqueries, and anything else you would use with a table.

Basic SELECT from a View

SELECT * FROM active_employees;

Output:

Diana (id=4) is excluded because she is not active. The view handles this filter automatically.

Filtering a View

You can add additional filters on top of the view's built-in logic:

SELECT name, department, salary
FROM active_employees
WHERE salary > 75000
ORDER BY salary DESC;

Output:

The view already filters for active employees. Your WHERE salary > 75000 is applied on top of that, so you get active employees earning above 75000.

Joining a View with Other Tables or Views

Views can be joined just like tables:

SELECT
    ae.name,
    ae.department,
    ae.salary,
    ds.avg_salary,
    ae.salary - ds.avg_salary AS diff_from_dept_avg
FROM active_employees ae
JOIN department_stats ds ON ae.department = ds.department_name
ORDER BY diff_from_dept_avg DESC;

Output:

Two views are joined together as if they were regular tables. The database resolves each view's underlying query at execution time.

Using a View in a Subquery

SELECT name, salary
FROM active_employees
WHERE salary > (SELECT avg_salary FROM department_stats WHERE department_name = 'Marketing');

Output:

Using Window Functions on a View

SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM active_employees;

Output:

A view is just a table from the perspective of any query that uses it.

Updatable Views

In some cases, you can use INSERT, UPDATE, and DELETE statements through a view, and the changes will be applied to the underlying table. These are called updatable views.

Requirements for Updatable Views

A view is generally updatable if it meets all of the following conditions:

Simple Updatable View Example

CREATE VIEW engineering_team AS
SELECT id, name, salary, hire_date, is_active
FROM employees
WHERE department_id = 1;

This view meets all the requirements: single table, includes the primary key (id), no aggregations, no joins. Let's test it.

SELECT through the View

SELECT * FROM engineering_team;

Output:

UPDATE through the View

UPDATE engineering_team
SET salary = 92000
WHERE name = 'Bob';

This modifies the employees table directly. Bob's salary is now 92000.

SELECT * FROM engineering_team;

Output:

INSERT through the View

INSERT INTO engineering_team (id, name, salary, hire_date, is_active)
VALUES (7, 'Grace', 85000, '2024-01-15', true);

DELETE through the View

DELETE FROM engineering_team
WHERE name = 'Grace';

This deletes the row from the underlying employees table.

Non-Updatable View Example

The department_stats view we created earlier uses GROUP BY and aggregate functions, making it non-updatable:

-- This will FAIL
UPDATE department_stats
SET avg_salary = 80000
WHERE department_name = 'Engineering';

Error: cannot update view "department_stats" (or similar depending on your database)

The database cannot determine which individual row(s) in the employees table should be modified to produce an average salary of 80000.

WITH CHECK OPTION

WITH CHECK OPTION ensures that any INSERT or UPDATE through the view still satisfies the view's WHERE condition. This prevents the "vanishing row" problem described earlier.

CREATE VIEW engineering_team_safe AS
SELECT id, name, salary, hire_date, is_active, department_id
FROM employees
WHERE department_id = 1
WITH CHECK OPTION;

Now if you try to insert a row with a different department:

-- This will FAIL because department_id = 2 violates the WHERE clause
INSERT INTO engineering_team_safe (id, name, salary, hire_date, is_active, department_id)
VALUES (8, 'Hank', 78000, '2024-03-01', true, 2);

Error: new row violates check option for view "engineering_team_safe"

The check option ensures every row inserted or updated through the view would still be visible through the view.

-- This SUCCEEDS because department_id = 1 satisfies the WHERE clause
INSERT INTO engineering_team_safe (id, name, salary, hire_date, is_active, department_id)
VALUES (8, 'Hank', 78000, '2024-03-01', true, 1);

CREATE OR REPLACE VIEW

As requirements evolve, you will need to modify existing views. The CREATE OR REPLACE VIEW statement lets you update a view's definition without dropping and recreating it.

Syntax

CREATE OR REPLACE VIEW view_name AS
SELECT new_column_list
FROM new_query;

Example: Adding a Column to an Existing View

Suppose we want to add is_active to our active_employees view:

CREATE OR REPLACE VIEW active_employees AS
SELECT
    e.id,
    e.name,
    d.name AS department,
    e.salary,
    e.hire_date,
    e.is_active      -- New column added
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.is_active = true;

The view is updated in place. Any queries or other views that reference active_employees continue to work. No data is lost because views do not store data.

Advantages Over DROP + CREATE

CREATE OR REPLACE VIEW is the safer approach because it:

• Does not invalidate existing permissions (grants)
• Does not break dependent views or application queries during the replacement
• Is a single atomic operation

Limitations

Most databases have restrictions on what you can change with CREATE OR REPLACE:

• You generally cannot remove columns that exist in the current view (this would break dependent queries)
• You generally can add new columns at the end
• You can change the underlying query logic (joins, filters, expressions)
• Column data types should remain compatible

Database-Specific Notes

For SQL Server, the equivalent is:

ALTER VIEW active_employees AS
SELECT
    e.id,
    e.name,
    d.name AS department,
    e.salary,
    e.hire_date,
    e.is_active
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.is_active = true;

Dropping Views

When a view is no longer needed, remove it with DROP VIEW.

Syntax

DROP VIEW view_name;

Example

DROP VIEW department_stats;

The view definition is removed from the database. The underlying tables and their data are completely unaffected.

DROP VIEW IF EXISTS

To avoid errors when dropping a view that might not exist:

DROP VIEW IF EXISTS department_stats;

Without IF EXISTS, dropping a non-existent view throws an error:

-- WRONG: Will error if the view doesn't exist
DROP VIEW department_stats;

Error: view "department_stats" does not exist

Dropping Multiple Views

Most databases support dropping several views at once:

DROP VIEW IF EXISTS
    active_employees,
    employee_summary,
    department_stats;

CASCADE vs RESTRICT

Some databases support CASCADE and RESTRICT options:

-- Drop the view and everything that depends on it
DROP VIEW department_stats CASCADE;

-- Drop only if nothing depends on it (default behavior)
DROP VIEW department_stats RESTRICT;

Views vs CTEs vs Temporary Tables

A common question is when to use a view versus a CTE versus a temporary table. Each serves a different purpose:

When to Use Each

• View: Reusable query that should be available to multiple users, multiple queries, and multiple applications. Examples: active_customers, monthly_revenue_report.
• CTE: One-off intermediate result within a single complex query. Examples: ranked subsets, recursive traversals.
• Temporary Table: Intermediate result that needs to be referenced multiple times across different queries within a session, especially when the computation is expensive. Examples: staging data during ETL, caching an expensive aggregation for multiple downstream queries.

Common Mistakes to Avoid

Mistake 1: Assuming Views Store Data

CREATE VIEW expensive_report AS
SELECT
    customer_name,
    SUM(amount) AS total_spent,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_name;

Every time you SELECT * FROM expensive_report, the aggregation query runs from scratch against the orders table. If orders has millions of rows, this can be slow.

If you need a "cached" view that stores precomputed results, look into materialized views (available in PostgreSQL, Oracle, and SQL Server). A materialized view stores the result physically and must be refreshed explicitly to pick up new data:

-- PostgreSQL: Materialized view
CREATE MATERIALIZED VIEW expensive_report AS
SELECT
    customer_name,
    SUM(amount) AS total_spent,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_name;

-- Refresh when needed
REFRESH MATERIALIZED VIEW expensive_report;

Mistake 2: Creating Views That Hide Too Much Complexity

While views simplify queries, stacking views on top of views on top of views can create an opaque system that is hard to debug and optimize:

-- View A
CREATE VIEW base_data AS SELECT ... FROM table1 JOIN table2 ...;

-- View B references View A
CREATE VIEW filtered_data AS SELECT ... FROM base_data WHERE ...;

-- View C references View B
CREATE VIEW report_data AS SELECT ... FROM filtered_data JOIN table3 ...;

-- View D references View C
CREATE VIEW final_report AS SELECT ... FROM report_data GROUP BY ...;

-- What's actually happening here?
SELECT * FROM final_report;

When a query against final_report is slow, you have to trace through four layers of view definitions to understand what is happening. The query optimizer may also struggle to produce an efficient plan across deeply stacked views.

Best practice: Limit view nesting to two levels at most. If your logic requires more steps, consider using a materialized view or restructuring the query.

Mistake 3: Trying to Use ORDER BY in a View Definition

In most databases, ORDER BY in a view definition is either ignored or not allowed:

-- WRONG: ORDER BY in the view itself (ignored or error in most databases)
CREATE VIEW sorted_employees AS
SELECT name, salary
FROM employees
ORDER BY salary DESC;

PostgreSQL: Allows it but ignores the order (view results are not guaranteed to be sorted). SQL Server: Throws an error unless TOP or OFFSET is used. MySQL: Allows it but the order is not guaranteed.

Fix: Apply ORDER BY in the outer query that reads from the view:

CREATE VIEW all_employees AS
SELECT name, salary FROM employees;

-- Apply ordering when querying
SELECT * FROM all_employees ORDER BY salary DESC;

Mistake 4: Not Considering Column Name Conflicts

When a view joins tables with identically named columns, you must use aliases to avoid ambiguity:

-- WRONG: Both tables have a column named "name"
CREATE VIEW team_info AS
SELECT
    e.name,       -- Employee name
    d.name        -- Department name (conflict!)
FROM employees e
JOIN departments d ON e.department_id = d.id;

Error: column "name" specified more than once

Fix: Use distinct aliases:

CREATE VIEW team_info AS
SELECT
    e.name AS employee_name,
    d.name AS department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

Real-World Pattern: Layered Reporting Views

Here is a practical pattern that demonstrates how views can build a clean reporting layer on top of raw data:

-- Layer 1: Clean, joined base data
CREATE VIEW v_order_details AS
SELECT
    o.id AS order_id,
    o.customer_name,
    o.order_date,
    o.amount,
    o.status,
    EXTRACT(YEAR FROM o.order_date) AS order_year,
    EXTRACT(MONTH FROM o.order_date) AS order_month
FROM orders o
WHERE o.status != 'cancelled';

-- Layer 2: Aggregated report on top of Layer 1
CREATE VIEW v_monthly_revenue AS
SELECT
    order_year,
    order_month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue,
    ROUND(AVG(amount), 2) AS avg_order_value,
    MIN(amount) AS min_order,
    MAX(amount) AS max_order
FROM v_order_details
GROUP BY order_year, order_month;

Now anyone can get the monthly revenue report with a simple query:

SELECT * FROM v_monthly_revenue ORDER BY order_year, order_month;

Output:

Cancelled orders are automatically excluded (handled by v_order_details). The business logic is defined once and consumed everywhere.

Listing and Inspecting Existing Views

Listing All Views

-- PostgreSQL
SELECT table_name FROM information_schema.views
WHERE table_schema = 'public';

-- MySQL
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- SQL Server
SELECT name FROM sys.views;

-- SQLite
SELECT name FROM sqlite_master WHERE type = 'view';

Viewing a View's Definition

-- PostgreSQL
SELECT definition FROM pg_views WHERE viewname = 'active_employees';

-- MySQL
SHOW CREATE VIEW active_employees;

-- SQL Server
EXEC sp_helptext 'active_employees';

-- SQLite
SELECT sql FROM sqlite_master WHERE type = 'view' AND name = 'active_employees';

This is invaluable when you need to understand or debug an existing view that someone else created.

Summary

SQL CREATE VIEW is one of the most practical tools for organizing and simplifying database access:

• A view is a saved SELECT query that behaves like a virtual table. It stores no data and always reflects the current state of the underlying tables.
• CREATE VIEW saves a query definition. CREATE OR REPLACE VIEW modifies an existing view without dropping it. DROP VIEW removes it.
• Views can be queried, filtered, joined, and used in subqueries exactly like regular tables.
• Updatable views allow INSERT, UPDATE, and DELETE on the underlying table, but only if the view meets strict criteria (single table, no aggregation, no DISTINCT, includes primary key).
• WITH CHECK OPTION prevents inserts and updates through a view that would create rows invisible to the view.
• Views have no inherent performance penalty since the optimizer merges the view definition with the outer query. For expensive queries that need caching, consider materialized views.
• Apply ORDER BY in the consuming query, not in the view definition.