SQL Views

Save a query as a named view, query it like a table, and use views to simplify reports and limit access.

Lesson 19 of 102Advanced18 min read

After a while you'll notice that some queries get written again and again. The join that shows every order with its customer and product name. The calculation of each customer's total spending. Copying a long query between reports is tedious, and when the logic changes you have to find and fix every copy.

A view solves this by saving a query in the database under a name. From then on you can select from the view exactly as if it were a table: filter it, sort it, join it to other tables. The view doesn't store its own copy of the data. Each time you use it, the database runs the saved query against the current tables, so the results are always up to date.

In this lesson you'll create and use views, see why they're "virtual", update and remove them, and learn how teams use views to simplify reporting and to show people only the data they're allowed to see.

Creating and using a view

SQL
CREATE VIEW order_details AS
SELECT o.id, c.name AS customer, p.name AS product, o.quantity
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN products p  ON p.id = o.product_id;

Now the three-table join has a name, and anyone can use it with a simple query:

SQL
SELECT customer, product FROM order_details WHERE quantity > 1;

A view's columns take the names, or aliases, from its SELECT list, which is why giving calculated columns clear aliases matters. Views appear alongside tables in database tools, and most people using them don't need to know how they're built.

Views are virtual

A normal view stores only the query, not any rows. That has two consequences:

  • Always current: if a new order is inserted, it appears in order_details straight away, with no refresh needed.
  • Same speed as the query: a view doesn't make a slow query faster, because the full query still runs every time. Good indexes on the underlying tables are what make views fast.

PostgreSQL also offers materialized views, which do store the result and are refreshed on demand with REFRESH MATERIALIZED VIEW. They suit expensive reports that don't need to be up to the second.

Changing and removing views

To change a view's query:

  • PostgreSQL and MySQL: CREATE OR REPLACE VIEW name AS SELECT ...
  • SQLite: DROP VIEW name; then CREATE VIEW name AS ... again

DROP VIEW IF EXISTS name; removes a view. Dropping a view never deletes any data, only the saved query. Some simple views, based on a single table without grouping, are updatable in MySQL and PostgreSQL: an UPDATE on the view changes the underlying table. In practice, most teams treat views as read-only.

Views for security and simpler reports

Views are a neat way to control what people see. A view can leave out sensitive columns, such as phone numbers or salaries, and filter rows, such as only this year's orders. In MySQL and PostgreSQL, you can then give a reporting user permission to read the view but not the underlying tables:

SQL
GRANT SELECT ON order_details TO reporting_user;

Views also keep business rules in one place. If "active customer" means "ordered in the last 90 days", define it once in a view, and every report that uses the view agrees.

SQL Views Syntax

View syntax

CREATE VIEW view_name AS
SELECT columns FROM tables WHERE condition;

SELECT * FROM view_name;

CREATE OR REPLACE VIEW view_name AS SELECT ...;   -- MySQL, PostgreSQL
DROP VIEW IF EXISTS view_name;

SQL Views Summary

  • CREATE VIEW name AS SELECT ... saves a query under a name.
  • Query a view like a table; it reruns its query each time, so results are current.
  • A normal view stores no data and isn't faster than its query.
  • Use CREATE OR REPLACE VIEW (or drop and recreate in SQLite) to change it, DROP VIEW to remove it.
  • Views simplify reports, hide sensitive columns and keep business rules in one place.

SQL Views Examples

Each example shows the code, the output it produces and what each part does.

Example 1

A view of order details

SQL
CREATE VIEW order_details AS
SELECT o.id,
       c.name AS customer,
       p.name AS product,
       o.quantity,
       o.quantity * p.price AS line_total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN products p  ON p.id = o.product_id;

SELECT customer, product, line_total
FROM order_details
WHERE line_total >= 20
ORDER BY line_total DESC;
Output
customer | product      | line_total
---------+--------------+-----------
Ana      | Backpack     | 34.99
Dev      | Desk lamp    | 24.5
Chloe    | Water bottle | 24.0
Line-by-line explanation
  1. Lines 1 to 9The view stores the join and the calculated line total.
  2. Lines 11 to 14Using it is as simple as querying a single table.
Example 2

Views are always up to date

SQL
SELECT COUNT(*) FROM order_details;

INSERT INTO orders (customer_id, product_id, quantity, order_date)
VALUES (5, 1, 3, '2024-07-12');

SELECT COUNT(*) FROM order_details;
SELECT customer, product, quantity FROM order_details WHERE customer = 'Ema';
Output
6
7
customer | product  | quantity
---------+----------+---------
Ema      | Notebook | 3
Line-by-line explanation
  1. Line 1Six orders before the insert.
  2. Lines 3 to 4A new order for Ema is added to the orders table, not to the view.
  3. Lines 6 to 7The view runs its query again, so the new order appears immediately.
Example 3

A summary view for reporting

SQL
CREATE VIEW category_summary AS
SELECT category,
       COUNT(*)   AS products,
       SUM(stock) AS units_in_stock,
       SUM(CASE WHEN stock = 0 THEN 1 ELSE 0 END) AS sold_out
FROM products
GROUP BY category;

SELECT * FROM category_summary ORDER BY units_in_stock DESC;
Output
category   | products | units_in_stock | sold_out
-----------+----------+----------------+---------
Stationery | 2        | 420            | 0
Kitchen    | 2        | 40             | 1
Home       | 1        | 22             | 0
Bags       | 1        | 15             | 0
Line-by-line explanation
  1. Line 4A CASE expression inside SUM counts the sold-out products in each category.
  2. Line 9The report query stays short, and every dashboard that uses the view shows the same numbers.

Common SQL Views Mistakes

Expecting a view to store a snapshot

Don't
-- "save today's numbers"
CREATE VIEW july_sales AS SELECT * FROM orders;   -- changes as orders change
Do
CREATE TABLE july_sales AS
SELECT * FROM orders WHERE order_date BETWEEN '2024-07-01' AND '2024-07-31';

A view always shows current data. Copy rows into a table when you need a fixed snapshot.

Unnamed calculated columns

Don't
CREATE VIEW totals AS
SELECT customer_id, SUM(quantity) FROM orders GROUP BY customer_id;
Do
CREATE VIEW totals AS
SELECT customer_id, SUM(quantity) AS items FROM orders GROUP BY customer_id;

Without an alias, the column gets an awkward name like "SUM(quantity)" that's hard to use later.

Using CREATE OR REPLACE in SQLite

Don't
CREATE OR REPLACE VIEW totals AS SELECT ...;   -- syntax error in SQLite
Do
DROP VIEW IF EXISTS totals;
CREATE VIEW totals AS SELECT ...;

SQL Views in Real Projects

Views in companies

Data teams publish a set of well-named views, such as active_customers and monthly_revenue, so analysts and dashboard tools don't need to know every join in the database. Database administrators give apps and staff access to views rather than raw tables, which keeps personal data like addresses and phone numbers out of reports.

SQL Views FAQ

Do views make queries faster?

An ordinary view doesn't: it's a saved query that runs against the tables each time you use it, so it performs like the underlying query. A materialised view stores the result and can be much faster to read, at the cost of being refreshed to stay up to date.

What is a materialised view?

A view whose result is stored on disk, like a table. In PostgreSQL, create it with CREATE MATERIALIZED VIEW and update it with REFRESH MATERIALIZED VIEW. It suits expensive reports that can be a little out of date. MySQL and SQLite don't have them; a summary table rebuilt on a schedule does the same job.

Can I insert or update rows through a view?

Simple views on one table, without aggregates, DISTINCT or GROUP BY, are usually updatable in PostgreSQL and MySQL, and changes go to the underlying table. Add WITH CHECK OPTION to reject changes that would fall outside the view's WHERE filter. SQLite views are read-only unless you add INSTEAD OF triggers.

Do views use the indexes of their tables?

Yes. Because the database merges the view's query into yours, filters on the view can still use indexes on the underlying tables. A filter on a calculated column of the view can't use an ordinary index, just as in a direct query.

What happens to a view if its table changes?

Renaming or dropping a column that the view uses breaks the view, and many databases refuse the change until the view is dropped or replaced. A view created with SELECT * stores the column list from creation time, so new columns don't appear until you recreate it. List columns explicitly in views.

SQL Views Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the statement that saves a query as a view.

 cheap_products 
SELECT name, price FROM products WHERE price < 10;
Multiple choiceeasy

How do you read data from a view called cheap_products?

Predict the outputmedium

A product's price changes from 9 to 12. What does cheap_products (price < 10) show afterwards?

Multiple choicemedium

What does DROP VIEW order_details; delete?

Multiple choicehard

Which is a good reason to create a view?

SQL Views Quiz

5 questions to check what you learned.

  1. What does a normal view store?

    • A copy of the data
    • An index
    • A saved query
    • A backup file

  2. Are view results up to date when the underlying tables change?

    • No, you must refresh normal views
    • Only once a day
    • Only in MySQL
    • Yes, the query runs again each time the view is used

  3. How do you change a view's query in SQLite?

    • DROP VIEW and then CREATE VIEW again
    • ALTER VIEW
    • CREATE OR REPLACE VIEW
    • UPDATE VIEW

  4. What is a materialized view?

    • A view with no columns
    • A view that stores its results and is refreshed on demand
    • A view that can't be dropped
    • A view on one table only

  5. How can a view help with data privacy?

    • It can expose only some columns and rows, and users can be granted access to the view alone
    • It encrypts the table
    • It deletes personal data
    • It prevents all queries

Related SQL topics

15Subqueries & CTEsUse the result of one query inside another with subqueries, IN, EXISTS, derived tables and WITH clauses.12JOINCombine rows from related tables with INNER JOIN and LEFT JOIN, using keys and table aliases.21Indexes & PerformanceSpeed up searches, joins and sorting with indexes, and check how a query runs with EXPLAIN.
Esc
↑↓ to navigateEnter to open