SQL Views Explained: Create, Use and Avoid the Pitfalls, With Examples

The same twenty-line report query has been copied into five dashboards, and each copy has drifted a little. One counts cancelled orders, while another does not. SQL views solve this by defining that logic once, in the database, under a clear name.

We ran every example below in SQLite 3.50, and the results shown come from that run. Views are standard SQL, but details such as updating through a view, materialized views and dependency handling vary between databases, and we point those out.

What is a view?

A view is a SELECT statement saved in the database with a name. When you query the view, the database runs that saved query. It acts like a table for reading, but a normal view does not hold its own copy of the data.

Think of a saved filter in a spreadsheet. The filter is not a second copy of the sheet. It is a way of looking at the same data, which always reflects the latest values.

The sample tables

CREATE TABLE customers (
  id INT PRIMARY KEY, name TEXT, city TEXT, email TEXT, credit_limit INT
);
CREATE TABLE orders (
  id INT PRIMARY KEY, customer_id INT, total INT, status TEXT
);

INSERT INTO customers VALUES
 (1,'Asha','Pune','asha@example.com',50000),
 (2,'Ben','Delhi','ben@example.com',20000),
 (3,'Chitra','Pune','chitra@example.com',80000);

INSERT INTO orders VALUES
 (101,1,1200,'paid'), (102,1,800,'paid'),
 (103,2,300,'pending'), (104,3,2500,'paid');

Create a view

This view summarizes each customer’s orders. It hides a join, grouping and a CASE expression behind one simple name:

CREATE VIEW customer_sales AS
SELECT c.id, c.name, c.city,
       COUNT(o.id) AS orders,
       COALESCE(SUM(CASE WHEN o.status = 'paid' THEN o.total END), 0) AS paid_total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name, c.city;

Now anybody can use it, without knowing how it works:

SELECT * FROM customer_sales ORDER BY paid_total DESC;

-- 3 | Chitra | Pune  | 1 | 2500
-- 1 | Asha   | Pune  | 2 | 2000
-- 2 | Ben    | Delhi | 1 |    0

SELECT name, paid_total FROM customer_sales WHERE city = 'Pune';

-- Asha   | 2000
-- Chitra | 2500

Ben appears with zero, because the view uses a LEFT JOIN and COALESCE. Decisions like this, made once in the view, are exactly what keep reports consistent. To refresh your understanding of the NULL handling, see SQL NULL values.

Views are always current

Since a normal view runs its query every time, new data appears immediately. We added a paid order for Ben and queried the view again:

INSERT INTO orders VALUES (105, 2, 900, 'paid');

SELECT name, paid_total FROM customer_sales WHERE name = 'Ben';

-- Ben | 900

There was no refresh step and no copy to keep in sync. This is the key difference from saving query results into a separate table.

Hiding columns

Views are often used to expose only the columns a person or application needs. Here, email addresses and credit limits are left out:

CREATE VIEW public_customers AS
SELECT id, name, city FROM customers;

-- columns: id, name, city

In databases with permissions, such as PostgreSQL, SQL Server and MySQL, you can grant a reporting user access to the view but not the underlying table. SQLite has no user accounts, so we could only show the column restriction itself. Treat a view as one layer of protection. Sensitive data still needs proper access control, and some databases offer row-level security for filtering rows by user.

Performance: views are not a speed boost

A common myth is that views make queries faster. A normal view is replaced by its definition when you query it, so the database does the same work as if you had written the full query yourself. Our execution plan showed the view’s join and grouping being run as part of the query:

EXPLAIN QUERY PLAN SELECT * FROM customer_sales WHERE city = 'Pune';

-- CO-ROUTINE customer_sales
-- SCAN c ...
-- SEARCH o USING AUTOMATIC COVERING INDEX (customer_id=?) LEFT-JOIN
-- USE TEMP B-TREE FOR GROUP BY
-- SCAN customer_sales

So a view over a slow query is still a slow query. Speed comes from indexes and good query design, as covered in SQL query optimization and SQL indexes explained. Watch out for views built on other views, too. Layers of nesting make it easy to join the same large table several times without noticing.

Materialized views

A materialized view does store its results, like a cached table. Reads are fast, but the data is only as fresh as the last refresh.

-- PostgreSQL
CREATE MATERIALIZED VIEW daily_sales AS
SELECT order_date, SUM(total) AS revenue FROM orders GROUP BY order_date;

REFRESH MATERIALIZED VIEW daily_sales;

Support varies. PostgreSQL and Oracle have materialized views, and SQL Server offers indexed views with their own rules. SQLite and MySQL lack them, so people use a regular summary table refreshed on a schedule. We did not run the PostgreSQL example for this article.

Can you update a view?

It depends on the database and the view. A simple view over a single table, without grouping or DISTINCT, can often be updated in PostgreSQL, MySQL and SQL Server, and the change passes through to the table. A view with joins or aggregates generally cannot, because the database cannot tell which row to change.

SQLite does not allow direct writes to views at all. Our attempt failed:

INSERT INTO customer_sales (name) VALUES ('X');

-- cannot modify customer_sales because it is a view

Some databases support INSTEAD OF triggers to make a view writable, and many offer WITH CHECK OPTION, which stops writes that would not be visible through the view. In practice, most teams keep views read-only and write to the tables.

Dependencies and breakage

A view depends on the tables it reads from. We created a view on a table, then dropped the table:

CREATE TABLE ghost AS SELECT 1 AS x;
CREATE VIEW v_ghost AS SELECT x FROM ghost;
DROP TABLE ghost;

SELECT * FROM v_ghost;
-- no such table: main.ghost

SQLite allowed the drop and only failed later, when the view was used. Other databases, such as PostgreSQL, refuse to drop a table that a view depends on, unless you add CASCADE, which removes the view as well. Either way, renaming or dropping a column can break views, so check them in your regression testing after schema changes.

There is also a subtle trap with SELECT * in a view. We created CREATE VIEW star AS SELECT * FROM customers, then added a column to the table. In SQLite, the view picked up the new column. Other databases, including PostgreSQL, expand the column list when the view is created, so new columns do not appear. So list the columns explicitly. It documents the view, and it behaves the same everywhere.

Managing views

Dropping a view only removes the saved query. In our test, the customers table still had all three rows after dropping public_customers. Keep view definitions in version control along with your migrations, so changes are reviewed like any other code.

View vs CTE vs table

Common mistakes

• Expecting a speed boost. A normal view runs its query each time.
• Using SELECT * in views. Behaviour differs between databases when tables change.
• Deep nesting. Views built on views hide expensive joins.
• Using ORDER BY inside a view. Order is not guaranteed when you query it, so sort in the outer query.
• Unclear names. A prefix such as v_, or a clear noun like customer_sales, helps.
• Forgetting views during migrations. Test them after every schema change.

Final thoughts

Views are a simple way to give important logic one home in the database. Use them to keep reports consistent and to hide unneeded columns, not as a performance trick. To start, find a query that appears in several places in your code, turn it into a view with explicit columns, and point each caller at it.

Frequently asked questions

Keep learning