SQL CREATE VIEW

A SQL view is a saved SELECT statement that you can query as if it were a table. It stores no data of its own — every time you query a view, the database engine re-runs the underlying SELECT behind the scenes and hands back the result. Views are one of the simplest, most powerful tools for organizing SQL: they let you package a complex join, filter, or calculation behind a short, memorable name, and they can restrict which columns and rows different users are allowed to see.

Overview: How Views Work

When you run CREATE VIEW, the database engine does not execute the query and save its results. Instead, it stores the text of your SELECT statement in the database’s system catalog (in SQLite, that’s the sqlite_master table; other engines use an information_schema.views table or similar). The view name becomes a pointer to that stored query.

From then on, whenever you reference the view — in a plain SELECT, inside a JOIN, in a WHERE clause subquery, anywhere a table name is legal — the engine substitutes the view’s stored SELECT in that spot, much like a subquery or a named macro. Because the substitution happens at query time, a view always reflects the current, live data in its base tables. There’s no staleness and no manual refresh step, unlike a materialized view (supported by PostgreSQL, Oracle, and others), which does physically store its result set and must be refreshed on a schedule or by hand. Plain SQL views — the kind this lesson covers, and the only kind SQLite supports — are never materialized.

Because a view is just a stored query, it can do anything a regular SELECT can: filter rows with WHERE, join multiple tables, aggregate with GROUP BY, compute derived columns, even reference other views. This makes views excellent for hiding complexity — analysts can query a tidy customer_totals view instead of re-writing a three-table join every time — and for access control, since you can grant someone permission to query a view while withholding direct access to the sensitive columns or rows in the underlying tables.

Syntax

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

Part	Meaning
CREATE VIEW	Defines a new view. Some engines (MySQL, PostgreSQL) support CREATE OR REPLACE VIEW to overwrite an existing one in a single statement — SQLite does not, so you must DROP then CREATE instead (see Common Mistakes).
IF NOT EXISTS	Optional. Prevents an error if a view with that name already exists; the statement simply does nothing in that case.
view_name	The identifier you’ll use to query the view later, just like a table name.
(column1, column2, ...)	Optional explicit column list for the view’s output. If omitted, the view’s columns take their names from the SELECT list (aliases included).
AS select_statement	The query the view wraps. It can include joins, WHERE, GROUP BY, HAVING, ORDER BY, subqueries, and window functions — virtually anything a normal SELECT supports.
DROP VIEW [IF EXISTS] view_name	Removes the view’s definition. It never touches the underlying tables or their data.

Examples

Example 1: A simple filtering view

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  salary REAL,
  hire_date TEXT
);

INSERT INTO employees (id, name, department, salary, hire_date) VALUES
  (1, 'Alice Chen', 'Engineering', 95000, '2021-03-15'),
  (2, 'Brian Ortiz', 'Sales', 62000, '2020-07-01'),
  (3, 'Carla Nguyen', 'Engineering', 88000, '2022-01-10'),
  (4, 'David Kim', 'Marketing', 71000, '2019-11-23');

CREATE VIEW engineering_employees AS
SELECT id, name, salary, hire_date
FROM employees
WHERE department = 'Engineering';

SELECT * FROM engineering_employees;

Result:

id	name	salary	hire_date
1	Alice Chen	95000	2021-03-15
3	Carla Nguyen	88000	2022-01-10

The view stores the filter WHERE department = 'Engineering'. Querying engineering_employees is now just as easy as querying a table, and the Sales and Marketing rows never appear — you don’t have to remember or repeat the filter condition every time.

Example 2: A view over a join

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT,
  country TEXT
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_date TEXT,
  amount REAL
);

INSERT INTO customers (customer_id, customer_name, country) VALUES
  (1, 'Nora Fielding', 'USA'),
  (2, 'Owen Bishop', 'UK'),
  (3, 'Priya Rao', 'Canada');

INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES
  (101, 1, '2024-01-05', 250.00),
  (102, 1, '2024-02-14', 130.50),
  (103, 2, '2024-03-01', 89.99);

CREATE VIEW customer_order_summary AS
SELECT c.customer_name, c.country, o.order_id, o.order_date, o.amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

SELECT * FROM customer_order_summary
WHERE country = 'USA'
ORDER BY order_date;

Result:

customer_name	country	order_id	order_date	amount
Nora Fielding	USA	101	2024-01-05	250.0
Nora Fielding	USA	102	2024-02-14	130.5

This view hides the join between customers and orders entirely. Anyone querying customer_order_summary just filters and sorts it like a normal table — the WHERE country = 'USA' and ORDER BY order_date in the outer query run on top of the view’s own join, which is exactly how views are meant to be used: as a foundation other queries build on.

Example 3: A view with aggregation

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT,
  country TEXT
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_date TEXT,
  amount REAL
);

INSERT INTO customers (customer_id, customer_name, country) VALUES
  (1, 'Nora Fielding', 'USA'),
  (2, 'Owen Bishop', 'UK'),
  (3, 'Priya Rao', 'Canada');

INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES
  (101, 1, '2024-01-05', 250.00),
  (102, 1, '2024-02-14', 130.50),
  (103, 2, '2024-03-01', 89.99);

CREATE VIEW customer_totals AS
SELECT c.customer_name, COUNT(o.order_id) AS order_count, SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name;

SELECT * FROM customer_totals
ORDER BY total_spent DESC;

Result:

customer_name	order_count	total_spent
Nora Fielding	2	380.5
Owen Bishop	1	89.99

Because the join in customer_totals is an inner join, Priya Rao — who has no orders — doesn’t appear at all. This is a common gotcha: a view’s join type quietly determines which rows are even eligible to show up, no matter what the outer query later asks for.

How Views Work Step by Step (Under the Hood)

Every SQL query, view or not, is logically processed in a fixed order: FROM (and any joins) first, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT. When a view’s name shows up in the FROM clause of an outer query, the engine effectively drops the view’s stored SELECT statement into that slot as a nested query block, and then keeps processing the outer query’s own WHERE/GROUP BY/SELECT/ORDER BY around it.

In Example 2, that means the engine conceptually runs something like SELECT * FROM (SELECT c.customer_name, c.country, o.order_id, o.order_date, o.amount FROM customers c JOIN orders o ON c.customer_id = o.customer_id) WHERE country = 'USA' ORDER BY order_date. Query optimizers are usually smart enough to merge the two layers into a single execution plan rather than materializing an intermediate result — pushing the outer WHERE country = 'USA' down so it can use an index on customers.country if one exists, rather than joining everything first and filtering afterward.

That last point matters for performance: a view itself has no storage and no index of its own. Every query against a view is really a query against its underlying tables, so indexing strategy always targets the base tables (see Common Mistakes below), not the view name.

Common Mistakes

Mistake 1: Using CREATE OR REPLACE VIEW in SQLite

CREATE OR REPLACE VIEW engineering_employees AS
SELECT id, name, salary
FROM employees
WHERE department = 'Engineering';

MySQL and PostgreSQL support CREATE OR REPLACE VIEW to redefine a view in one statement, but SQLite’s grammar has no OR REPLACE clause for views, so this raises a syntax error. The portable fix works everywhere, including SQLite:

DROP VIEW IF EXISTS engineering_employees;

CREATE VIEW engineering_employees AS
SELECT id, name, salary
FROM employees
WHERE department = 'Engineering';

Mistake 2: Trying to index a view directly

CREATE VIEW engineering_employees AS
SELECT id, name, department, salary
FROM employees
WHERE department = 'Engineering';

CREATE INDEX idx_view_department ON engineering_employees(department);

This fails because a view has no physical storage to attach an index to — SQLite rejects it outright (“views may not be indexed”). If you want the underlying scan to be fast, index the base table column that the view actually filters or joins on:

CREATE INDEX idx_employees_department ON employees(department);

Mistake 3: Updating data through a view without an INSTEAD OF trigger

CREATE VIEW engineering_employees AS
SELECT id, name, department, salary
FROM employees
WHERE department = 'Engineering';

UPDATE engineering_employees SET salary = 100000 WHERE id = 1;

Plain views are read-only in SQLite. Running UPDATE, INSERT, or DELETE against one fails with an error like “cannot modify engineering_employees because it is a view,” unless you’ve explicitly defined an INSTEAD OF trigger that tells SQLite what the modification should do to the base table. Until you’ve set that up, write DML against the real table instead:

UPDATE employees SET salary = 100000 WHERE id = 1;

Best Practices

• Give views clear, distinguishing names (a vw_ prefix or _view suffix) so nobody mistakes them for base tables when browsing the schema.
• Keep each view focused on one purpose rather than trying to satisfy every possible future query — narrow, well-named views are easier to reuse and reason about than one giant do-everything view.
• Avoid deep chains of views built on views on views; each extra layer makes the logic harder to trace and can make it harder for the optimizer to simplify the final plan.
• Always index the underlying base tables, never the view — the view has nothing of its own to index.
• Use views to enforce column- and row-level access control: grant a role access to a view that exposes only the columns and rows it should see, and keep direct table access more restricted.
• Document what each view is for (in a comment or a data dictionary) since the raw SQL alone doesn’t explain business intent.
• Prefer DROP VIEW IF EXISTS followed by CREATE VIEW when redefining a view, since CREATE OR REPLACE VIEW isn’t portable to every database.
• Be deliberate before making a view updatable — either keep it read-only for reporting, or explicitly design INSTEAD OF triggers so writes behave predictably.

Practice Exercises

Exercise 1: Using the employees table structure from Example 1, write a view named high_earners that lists the name and salary of every employee whose salary is greater than 80000.

Exercise 2: Using the customers and orders tables from Example 2, write a view named orders_2024 that shows customer_name, order_id, and amount for orders placed in 2024 (hint: filter with order_date LIKE '2024%'), sorted by amount descending.

Exercise 3: Extend the customer_totals view from Example 3 to also include each customer’s average order amount using AVG(amount). What would you name that new column, and would you expect Priya Rao to appear in the result? Why or why not?

Summary

• A view is a stored SELECT statement queried like a table — it holds no data of its own and always reflects live data from its base tables.
• Basic syntax: CREATE VIEW view_name AS SELECT ...;; remove one with DROP VIEW view_name;.
• Views can wrap joins, filters, and aggregations, letting you hide complexity behind a simple, reusable name.
• When a view is queried, the engine substitutes its stored query into the outer query and optimizes the combined plan — performance still depends entirely on indexes on the underlying base tables.
• SQLite has no CREATE OR REPLACE VIEW; use DROP VIEW IF EXISTS followed by CREATE VIEW instead.
• You cannot index a view directly, and you cannot write to a plain view without an INSTEAD OF trigger.
• Views are a strong tool for readability, reuse, and access control — keep them focused and avoid stacking too many layers.