Simplifying Queries with Views

Written with a little help from AI, reviewed and edited by a human.

Sometimes you write the same complex query over and over — like joining three tables, filtering by certain conditions, and calculating aggregates. Views let you save a query and treat it like a table. It’s like creating a shortcut for a frequently used query.

Real life example

Imagine you’re a teacher and you often need to see a list of “students who passed” (scored 70 or above). Instead of filtering your grade book every single time, you create a separate sheet called “Passed Students” that automatically shows only those students.

Whenever you look at that sheet, it pulls the latest data from the main grade book and filters it for you. You don’t maintain it manually — it’s just a “view” of the data filtered in a specific way.

That’s what a SQL view does — it’s a saved query that looks like a table but doesn’t store data itself.

What is a view?

A view is a virtual table based on a SQL query. It doesn’t store data — it runs the query every time you access it and shows you the results.

Think of it like a saved filter or a dynamic report.

Creating a view

Let’s say you have a students table:

id	name	score
1	Alice	85
2	Bob	92
3	Charlie	65
4	Diana	78
5	Eve	55

You often need to see only students who passed (score >= 70). Instead of writing this query every time:

SELECT * FROM students WHERE score >= 70;

You can create a view:

CREATE VIEW passed_students AS
SELECT * FROM students WHERE score >= 70;

Now you can query the view like it’s a table:

SELECT * FROM passed_students;

Result:

id	name	score
1	Alice	85
2	Bob	92
4	Diana	78

The view automatically runs the underlying query and returns the filtered results.

Views are always up-to-date

A view doesn’t store data — it’s just a saved query. Every time you query the view, it fetches the latest data from the underlying tables.

If you add a new student who passed:

INSERT INTO students (name, score) VALUES ('Frank', 88);

And then query the view:

SELECT * FROM passed_students;

Frank will appear in the results. The view always reflects the current state of the data.

Views with JOINs

Views are especially useful for complex queries with JOINs. Let’s say you have two tables:

users

id	name
1	Alice
2	Bob

posts

id	title	user_id
1	First post	1
2	Hello world	1
3	SQL is fun	2

You often need to see posts with author names. Instead of writing the JOIN every time:

CREATE VIEW posts_with_authors AS
SELECT
  posts.id,
  posts.title,
  users.name AS author
FROM posts
INNER JOIN users ON posts.user_id = users.id;

Now you can query it simply:

SELECT * FROM posts_with_authors;

Result:

id	title	author
1	First post	Alice
2	Hello world	Alice
3	SQL is fun	Bob

Much cleaner than rewriting the JOIN every time.

Views with aggregates

You can create views that calculate aggregates:

CREATE VIEW user_post_counts AS
SELECT
  users.id,
  users.name,
  COUNT(posts.id) AS post_count
FROM users
LEFT JOIN posts ON users.id = posts.user_id
GROUP BY users.id, users.name;

Now you can easily see how many posts each user has:

SELECT * FROM user_post_counts;

Result:

id	name	post_count
1	Alice	2
2	Bob	1

Updating data through views

In some cases, you can INSERT, UPDATE, or DELETE through a view, and it will modify the underlying table:

UPDATE passed_students SET score = 90 WHERE name = 'Alice';

This updates the students table (Alice’s score becomes 90).

But this only works for simple views. If the view has JOINs, aggregates, or complex logic, you usually cannot modify data through it.

Most of the time, views are used for reading data, not writing.

Dropping a view

If you no longer need a view, you can drop it:

DROP VIEW passed_students;

This removes the view but doesn’t affect the underlying students table. The data is still there — you just removed the saved query.

Materialized views (advanced)

A materialized view is a view that does store data. It’s like a cached version of the query result.

CREATE MATERIALIZED VIEW passed_students AS
SELECT * FROM students WHERE score >= 70;

The difference:

• Regular view: Runs the query every time you access it (always up-to-date, but can be slow for complex queries)
• Materialized view: Stores the result once (fast to query, but can be out-of-date)

You manually refresh a materialized view to update it:

REFRESH MATERIALIZED VIEW passed_students;

Not all databases support materialized views (PostgreSQL does, SQLite and MySQL don’t).

When to use views

Use views when:

• You have a complex query that you run frequently
• You want to simplify access to data for other users or applications
• You want to hide complexity (like JOINs) behind a simple “table”
• You want to enforce a specific filter (like “only show active users”)

When NOT to use views

Don’t use views if:

• The underlying query is very slow and runs every time (consider indexes or materialized views instead)
• You need to modify data (views for updates are tricky)
• The logic is so simple that a view doesn’t add value

Views vs subqueries

You might wonder: what’s the difference between a view and a subquery?

View:

CREATE VIEW passed_students AS SELECT * FROM students WHERE score >= 70;

SELECT * FROM passed_students;

Subquery:

SELECT * FROM (
  SELECT * FROM students WHERE score >= 70
) AS passed_students;

Both work, but views are:

• Reusable — You define it once and use it many times
• Easier to read — SELECT * FROM passed_students is clearer than a nested subquery

Subqueries are fine for one-off queries. Views are better for queries you use repeatedly.

Security and access control

Views can be used to control what data users can see. For example, you might have a users table with sensitive columns like password_hash:

CREATE VIEW public_users AS
SELECT id, name, email FROM users;

You grant other users access to public_users but not to the underlying users table. They can see names and emails but not passwords.

Summary

• A view is a saved query that acts like a virtual table
• Views don’t store data — they run the query each time you access them
• Use CREATE VIEW to define a view, DROP VIEW to remove it
• Views are great for simplifying complex queries, especially JOINs and aggregates
• You can query views just like tables
• Views are always up-to-date because they query the underlying tables
• Materialized views store the result for faster access but require manual refresh