CREATE VIEW
In this page:
What is a View?
A VIEW is a virtual table that stores a saved SELECT query rather than actual data — querying the view re-runs that underlying query, so the results always reflect the live state of the real tables.
Example: What is a View?
CREATE TABLE orders (id INT, total INT);
INSERT INTO orders VALUES (1, 500), (2, 30);
CREATE VIEW big_orders AS SELECT * FROM orders WHERE total > 100;
SELECT * FROM big_orders;Creating a Simple View
Wrapping a complex filter or calculation inside a view lets you save that logic once and reuse it everywhere, so anyone querying the view gets the same filtered subset of data without retyping the original conditions.
Example: Creating a Simple View
CREATE TABLE orders (id INT, status TEXT, total INT);
INSERT INTO orders VALUES (1, 'paid', 500), (2, 'unpaid', 30);
CREATE VIEW paid_orders AS SELECT * FROM orders WHERE status = 'paid';
SELECT * FROM paid_orders;Querying a View
Once created, a view behaves like a regular table for read purposes — you can add WHERE clauses, ORDER BY, and other query features directly against it exactly as you would against any base table.
Example: Querying a View
CREATE TABLE orders (id INT, total INT);
INSERT INTO orders VALUES (1, 500), (2, 30), (3, 200);
CREATE VIEW big_orders AS SELECT * FROM orders WHERE total > 100;
SELECT * FROM big_orders ORDER BY total DESC;Views with Joined Tables
Views shine when a query joins several tables together, since they let you hide that join complexity behind a single, simple name that other developers or reports can query without needing to know the underlying schema.
Example: Views with Joined Tables
CREATE TABLE customers (id INT, name TEXT);
CREATE TABLE orders (id INT, customer_id INT, total INT);
INSERT INTO customers VALUES (1, 'Amit');
INSERT INTO orders VALUES (101, 1, 500);
CREATE VIEW order_summary AS
SELECT customers.name, orders.total
FROM orders JOIN customers ON orders.customer_id = customers.id;
SELECT * FROM order_summary;Security Benefits of Views
Views also serve as a security layer: you can build a view that exposes only certain columns, then grant users access to the view instead of the underlying table, keeping sensitive columns completely out of reach.
Example: Security Benefits of Views
CREATE TABLE employees (id INT, name TEXT, salary INT);
INSERT INTO employees VALUES (1, 'Amit', 50000);
CREATE VIEW public_employees AS SELECT id, name FROM employees;
SELECT * FROM public_employees;Chapter Quiz — Complete all 5 topics to unlock
0/5 topics done
Complete these topics first: