SQL VIEWS

What are SQL VIEWS and how do they work?

A VIEW is a virtual table defined by a stored SELECT query that returns data dynamically when referenced.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL VIEWS: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, Redshift, BigQuery, DuckDB

SQL VIEWS Full Explanation

A VIEW is an object that saves a query’s text in the database catalog and exposes the result set as if it were a table. Whenever you select from the view, the database re-executes the underlying query and returns the latest data (unless the view is materialized or cached by the engine). Views can simplify complex joins, encapsulate business logic, enforce row-level security, and provide limited column visibility. They do not store data themselves, consume almost no disk space, and automatically reflect changes in the base tables. Views can be updatable if the query meets certain rules (single base table, no aggregations, etc.), and you can add WITH CHECK OPTION to restrict writes that violate the view definition. Some dialects support additional clauses such as RECURSIVE, OR REPLACE, SECURITY DEFINER, and MATERIALIZED. Dropping a view removes only the definition, not the underlying data. Use views to build reusable, versioned, and governed query layers on top of raw tables.

SQL VIEWS Syntax

-- create
CREATE [OR REPLACE] [TEMP | TEMPORARY] VIEW view_name [ (column_list) ] AS
SELECT ...
[WITH [CASCADED] CHECK OPTION];

-- alter
ALTER VIEW view_name RENAME TO new_name;
ALTER VIEW view_name ALTER COLUMN col SET DATA TYPE new_type;

-- drop
DROP VIEW [IF EXISTS] view_name [CASCADE | RESTRICT];

SQL VIEWS Parameters

view_name (identifier) - Name of the view to create.
column_list (identifier list) - Optional explicit column names for the view.
SELECT ... (query) - Any valid SELECT statement whose result defines the view.
OR REPLACE (keyword) - Overwrites an existing view.
TEMP (TEMPORARY) - keyword|||View lives only in the current session.
WITH CHECK OPTION (keyword) - Prevents writes that would make rows invisible through the view.
CASCADED (keyword) - Applies CHECK OPTION to underlying views as well.

Example Queries Using SQL VIEWS

-- 1. Simplify a join
CREATE VIEW active_customers AS
SELECT c.id, c.name, o.last_order_date
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.status = 'active';

-- 2. Row-level security
CREATE VIEW sales_us AS
SELECT * FROM sales WHERE country = 'US'
WITH CHECK OPTION;

-- 3. Update through updatable view
UPDATE sales_us SET amount = amount * 1.05 WHERE id = 101;

-- 4. Replace a view after schema change
CREATE OR REPLACE VIEW active_customers AS
SELECT c.id, c.name, o.last_order_date, c.segment
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.status = 'active';

-- 5. Remove the view
drop view if exists active_customers;

Expected Output Using SQL VIEWS

active_customers is added to the catalog. Selecting from it returns only active customers with their last order date.
sales_us limits rows to US sales and blocks inserts/updates that violate the country filter.
The UPDATE succeeds because sales_us is updatable and the row remains visible.
The view definition is replaced without needing to drop dependent objects.
The catalog entry is removed; base tables stay intact.

Use Cases with SQL VIEWS

Abstract complex joins and calculations behind a simple object.
Provide business-friendly column names and hide sensitive fields.
Enforce row or column level security without duplicating data.
Offer backward compatibility after schema refactors.
Facilitate code reuse by letting downstream queries build on the view instead of raw tables.
Expose stable schemas to BI tools or API layers.

Common Mistakes with SQL VIEWS

Assuming views improve performance by default; most re-execute the underlying query.
Updating a view that is not updatable, leading to errors.
Forgetting to include WITH CHECK OPTION and unintentionally allowing data that violates the view filter.
Believing that dropping a view deletes data; it only removes the definition.
Overusing nested views, which can cause hard-to-debug performance issues.

First Introduced In

ANSI SQL-86

Frequently Asked Questions

Do SQL views store data?

Ordinary views do not store data. They keep only the query definition. Each time you query the view, the database executes the underlying SELECT to return current results. Materialized views differ because they cache data on disk.

Can I update rows through a view?

You can update, insert, or delete rows through a view if it is updatable. The main requirements are that the view references a single base table and avoids constructs like GROUP BY, DISTINCT, UNION, or window functions. Use WITH CHECK OPTION to enforce that modified rows still satisfy the view’s filter.

How do views impact performance?

A view neither inherently speeds up nor slows down queries. The optimizer expands the view into its underlying query, so execution time matches running that query directly. Performance problems usually stem from missing indexes or chains of nested views, not the view object itself.

What is the difference between a view and a materialized view?

A standard view is virtual and always reflects current base table data. A materialized view physically stores the result set, enabling faster reads but requiring manual or automatic refreshes to stay up to date.