SQL Views: CREATE VIEW Syntax, Examples and Use Cases

Many reports need the same thing: each order with its customer's name and city. That takes a join every time. A view stores the join under a name:

CREATE VIEW order_summary AS
SELECT o.id AS order_id,
       o.order_date,
       u.first_name || ' ' || u.last_name AS customer,
       u.city,
       o.status,
       o.total
FROM orders o
JOIN users u ON u.id = o.user_id;

After that, anyone can write SELECT * FROM order_summary WHERE status = 'delivered' without knowing how the tables are joined. Querying the view runs the stored SELECT, so the result is always current. Here is what the view returns, produced by running its query directly:

SELECT o.id AS order_id,
       o.order_date,
       u.first_name || ' ' || u.last_name AS customer,
       u.city,
       o.status,
       o.total
FROM orders o
JOIN users u ON u.id = o.user_id
ORDER BY o.id
LIMIT 6;

▶ Run this query in the SQL Playground

Hiding complex joins

A view over four tables turns "revenue by product category per month" into a simple query for everyone else:

CREATE VIEW category_monthly_sales AS
SELECT strftime('%Y-%m', o.order_date) AS month,
       p.category,
       SUM(oi.quantity) AS units,
       ROUND(SUM(oi.subtotal), 2) AS revenue
FROM order_items oi
JOIN orders o   ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
GROUP BY month, p.category;

SELECT strftime('%Y-%m', o.order_date) AS month,
       p.category,
       SUM(oi.quantity) AS units,
       ROUND(SUM(oi.subtotal), 2) AS revenue
FROM order_items oi
JOIN orders o   ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
WHERE o.order_date >= '2024-12-01'
GROUP BY month, p.category
ORDER BY revenue DESC;

▶ Run this query in the SQL Playground

Users of the view would write SELECT * FROM category_monthly_sales WHERE month = '2024-12'. Most databases push that filter into the view's query, so it is not slower than writing the full query by hand.

Restricting what people can see

A view can leave out sensitive columns, such as salary, and you can grant access to the view without granting access to the table:

CREATE VIEW employee_directory AS
SELECT first_name, last_name, email, department, title, city
FROM employees
WHERE status = 'active';

GRANT SELECT ON employee_directory TO reporting_role;   -- PostgreSQL, MySQL, SQL Server

SELECT first_name, last_name, email, department, title, city
FROM employees
WHERE status = 'active'
ORDER BY last_name
LIMIT 5;

▶ Run this query in the SQL Playground

SQLite has no user accounts or GRANT, so this use applies to server databases.

One definition of a business rule

If "revenue" means "orders that were not cancelled or refunded", a view can hold that rule so every report uses the same definition:

SELECT COUNT(*) AS counted_orders,
       ROUND(SUM(total), 2) AS revenue
FROM orders
WHERE status NOT IN ('cancelled', 'refunded');

▶ Run this query in the SQL Playground

A view is like a CTE that is saved in the database and can be used by any query and any user. A temporary table is a copy of data taken at one moment; a view is not.

A materialized view stores the result of its query on disk. Reading it is as fast as reading a table, and it can be indexed, but the data is only as fresh as the last refresh:

-- PostgreSQL
CREATE MATERIALIZED VIEW category_revenue AS
SELECT p.category, SUM(oi.subtotal) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY p.category;

REFRESH MATERIALIZED VIEW category_revenue;

Use one when the query is expensive, read often, and a little staleness is acceptable, such as a dashboard updated every hour.

Simple views, over one table without GROUP BY, DISTINCT, aggregates or UNION, are usually updatable: INSERT, UPDATE and DELETE on the view change the underlying table.

CREATE VIEW pending_orders AS
SELECT id, user_id, status, total
FROM orders
WHERE status = 'pending'
WITH CHECK OPTION;

UPDATE pending_orders SET total = total * 0.9 WHERE id = 70;   -- changes orders
UPDATE pending_orders SET status = 'shipped' WHERE id = 70;    -- rejected by CHECK OPTION

WITH CHECK OPTION rejects changes that would move a row out of the view, here a status other than pending. SQLite views are read-only; an INSTEAD OF trigger can make them accept changes.

A normal view is not faster than its query, because it is the query. The database merges the view definition into your statement and optimizes the whole thing. Things to watch:

Five errors and how to avoid them.

-- "the view shows last month's numbers"
CREATE VIEW month_end AS SELECT ... FROM orders;

CREATE TABLE month_end_2024_12 AS
SELECT ... FROM orders WHERE order_date < '2025-01-01';

CREATE VIEW v AS SELECT * FROM employees;

CREATE VIEW v AS
SELECT id, first_name, last_name, department
FROM employees;

DROP VIEW order_summary;  -- "deletes the orders"

-- DROP VIEW removes the saved query only;
-- DELETE FROM a_table removes rows

v_report -> v_sales -> v_orders_clean -> v_orders_base -> orders

v_report -> orders (and the tables it joins)

UPDATE category_monthly_sales
SET revenue = 0
WHERE category = 'Toys';

UPDATE order_items SET ...
WHERE product_id IN (SELECT id FROM products WHERE category = 'Toys');

4 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy Write the SELECT for a view called active_catalog that lists name, category and price of products whose status is active. Show the first 6 by name. Table: products

   Show solution

   SQLite · runs in the Playground

   SELECT name, category, price
   FROM products
   WHERE status = 'active'
   ORDER BY name
   LIMIT 6;

   ▶ Run this query in the SQL Playground

   In a local database, CREATE VIEW active_catalog AS followed by this SELECT (without ORDER BY and LIMIT) would save it.

2. 2Medium Write the query behind a customer_spend view: each customer's name, number of orders and total spent. Show the top 5. Table: orders, users

   Show solution

   SQLite · runs in the Playground

   SELECT u.id, u.first_name || ' ' || u.last_name AS customer,
          COUNT(o.id) AS orders,
          ROUND(SUM(o.total), 2) AS spent
   FROM users u
   JOIN orders o ON o.user_id = u.id
   GROUP BY u.id
   ORDER BY spent DESC
   LIMIT 5;

   ▶ Run this query in the SQL Playground

   An aggregated view like this is read-only: there is no single row to update.

3. 3Medium Write the query for a view that shows headcount and average salary per department without exposing individual salaries. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT department,
          COUNT(*) AS employees,
          ROUND(AVG(salary)) AS avg_salary
   FROM employees
   GROUP BY department
   ORDER BY department;

   ▶ Run this query in the SQL Playground

   Granting access to this view shares the averages but not anyone's pay.

4. 4Medium Check how many views and tables the Playground database has. Table: sqlite_master

   Show solution

   SQLite · runs in the Playground

   SELECT type, COUNT(*) AS objects
   FROM sqlite_master
   WHERE type IN ('table', 'view')
   GROUP BY type;

   ▶ Run this query in the SQL Playground

   The Playground has tables only. In your own SQLite database, views you create appear here with type = 'view'.