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_viewforever - 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:
| id | name | budget |
|---|---|---|
| 1 | Engineering | 500000 |
| 2 | Sales | 300000 |
| 3 | Marketing | 250000 |
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:
| id | name | department_id | salary | hire_date | is_active |
|---|---|---|---|---|---|
| 1 | Alice | 1 | 95000 | 2020-03-15 | true |
| 2 | Bob | 1 | 88000 | 2021-06-01 | true |
| 3 | Charlie | 2 | 72000 | 2019-11-20 | true |
| 4 | Diana | 2 | 68000 | 2022-01-10 | false |
| 5 | Eve | 3 | 74000 | 2020-08-05 | true |
| 6 | Frank | 3 | 70000 | 2023-02-14 | true |
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:
| id | customer_name | order_date | amount | status |
|---|---|---|---|---|
| 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 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:
| id | name | department | salary | hire_date |
|---|---|---|---|---|
| 1 | Alice | Engineering | 95000.00 | 2020-03-15 |
| 2 | Bob | Engineering | 88000.00 | 2021-06-01 |
| 3 | Charlie | Sales | 72000.00 | 2019-11-20 |
| 5 | Eve | Marketing | 74000.00 | 2020-08-05 |
| 6 | Frank | Marketing | 70000.00 | 2023-02-14 |
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:
| name | department | salary |
|---|---|---|
| Alice | Engineering | 95000.00 |
| Bob | Engineering | 88000.00 |
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:
| name | department | salary | avg_salary | diff_from_dept_avg |
|---|---|---|---|---|
| Alice | Engineering | 95000.00 | 91500.00 | 3500.00 |
| Eve | Marketing | 74000.00 | 72000.00 | 2000.00 |
| Charlie | Sales | 72000.00 | 72000.00 | 0.00 |
| Frank | Marketing | 70000.00 | 72000.00 | -2000.00 |
| Bob | Engineering | 88000.00 | 91500.00 | -3500.00 |
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:
| name | salary |
|---|---|
| Alice | 95000.00 |
| Bob | 88000.00 |
| Eve | 74000.00 |
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:
| name | department | salary | dept_rank |
|---|---|---|---|
| Alice | Engineering | 95000.00 | 1 |
| Bob | Engineering | 88000.00 | 2 |
| Eve | Marketing | 74000.00 | 1 |
| Frank | Marketing | 70000.00 | 2 |
| Charlie | Sales | 72000.00 | 1 |
A view is just a table from the perspective of any query that uses it.
When you write SELECT * FROM active_employees WHERE salary > 75000, the database does not first compute all active employees and then filter. Most databases merge the view definition with your outer query through a process called view resolution or view expansion. The optimizer produces a single execution plan as if you had written the full query directly. This means views generally have no performance penalty compared to writing the underlying query manually.
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:
| Requirement | Why |
|---|---|
| References exactly one base table | The database must know which table to modify |
| Includes the table's primary key | The database must identify which row to update |
No DISTINCT | Duplicate elimination makes row mapping ambiguous |
No GROUP BY or aggregate functions | Aggregated results do not map to individual rows |
No UNION, INTERSECT, or EXCEPT | Combined result sets do not map to a single table |
No subqueries in SELECT list (varies by DB) | Complex expressions may not be reversible |
| No window functions | Computed values do not map back to storable values |
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:
| id | name | salary | hire_date | is_active |
|---|---|---|---|---|
| 1 | Alice | 95000.00 | 2020-03-15 | true |
| 2 | Bob | 88000.00 | 2021-06-01 | true |
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:
| id | name | salary | hire_date | is_active |
|---|---|---|---|---|
| 1 | Alice | 95000.00 | 2020-03-15 | true |
| 2 | Bob | 92000.00 | 2021-06-01 | true |
INSERT through the View
INSERT INTO engineering_team (id, name, salary, hire_date, is_active)
VALUES (7, 'Grace', 85000, '2024-01-15', true);
This insert will succeed, but notice that the view does not include department_id. The new row will have department_id = NULL in the underlying employees table, which means the row will not appear in the engineering_team view (since the view filters WHERE department_id = 1). The row exists in the table but is invisible through the view.
This is a common source of confusion with updatable views. Some databases offer WITH CHECK OPTION to prevent this (covered below).
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);
Use WITH CHECK OPTION whenever you create an updatable view that filters rows. It acts as a guardrail preventing data that violates the view's conditions from being inserted or updated through the view.
Two levels are available in the SQL standard:
WITH LOCAL CHECK OPTION: Only checks the current view's conditionsWITH CASCADED CHECK OPTION(default in most databases): Checks conditions from all underlying views
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
| Approach | Permissions Preserved? | Dependent Objects Break? | Atomic? |
|---|---|---|---|
DROP VIEW then CREATE VIEW | No, must re-grant | Yes, briefly | No |
CREATE OR REPLACE VIEW | Yes | No | Yes |
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
If you need to make changes that CREATE OR REPLACE does not allow (such as removing columns or reordering them), you must DROP the view first and then CREATE it fresh. Be prepared to re-grant permissions and fix any dependent objects.
Database-Specific Notes
| Database | CREATE OR REPLACE VIEW Support |
|---|---|
| PostgreSQL | Fully supported. Cannot remove columns. Can add columns. |
| MySQL | Fully supported. More flexible with column changes. |
| SQL Server | Uses ALTER VIEW instead of CREATE OR REPLACE VIEW. |
| Oracle | Fully supported. |
| SQLite | Not supported. Must DROP and CREATE. |
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;
CASCADE will silently drop other views that depend on the view being dropped. Use it carefully, especially in production. Always check for dependencies first:
-- PostgreSQL: Check what depends on a view
SELECT dependent_ns.nspname AS schema, dependent_view.relname AS view_name
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class AS dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN pg_namespace dependent_ns ON dependent_view.relnamespace = dependent_ns.oid
WHERE pg_depend.refobjid = 'department_stats'::regclass;
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:
| Feature | View | CTE | Temporary Table |
|---|---|---|---|
| Persistence | Permanent (until dropped) | Single query only | Single session only |
| Stores data? | No (virtual) | No (virtual) | Yes (physical) |
| Reusable across queries? | Yes | No | Yes (within session) |
| Shareable with other users? | Yes | No | No |
| Can be indexed? | No (except materialized views) | No | Yes |
| Defined with | CREATE VIEW | WITH clause | CREATE TEMP TABLE |
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;
Regular views are always up to date because they execute their query live. Materialized views are snapshots that may be stale until refreshed. Choose based on your needs: freshness (regular view) vs. performance (materialized view).
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;
Views define what data to return, not how to present it. Ordering is a presentation concern and belongs in the query that consumes the view, not in the view itself.
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:
| order_year | order_month | order_count | total_revenue | avg_order_value | min_order | max_order |
|---|---|---|---|---|---|---|
| 2024 | 1 | 2 | 6800.00 | 3400.00 | 2500.00 | 4300.00 |
| 2024 | 2 | 2 | 7000.00 | 3500.00 | 1800.00 | 5200.00 |
| 2024 | 3 | 1 | 2750.00 | 2750.00 | 2750.00 | 2750.00 |
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
SELECTquery that behaves like a virtual table. It stores no data and always reflects the current state of the underlying tables. CREATE VIEWsaves a query definition.CREATE OR REPLACE VIEWmodifies an existing view without dropping it.DROP VIEWremoves it.- Views can be queried, filtered, joined, and used in subqueries exactly like regular tables.
- Updatable views allow
INSERT,UPDATE, andDELETEon the underlying table, but only if the view meets strict criteria (single table, no aggregation, noDISTINCT, includes primary key). WITH CHECK OPTIONprevents 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 BYin the consuming query, not in the view definition.
- You find yourself writing the same join/filter/computation in multiple queries
- You want to restrict access to certain columns or rows for specific users
- You want to create a stable interface over tables whose structure may change
- You need a clean reporting layer on top of raw transactional data