Getting to Know SQL Views (Bite-size Article)

Introduction

In a previous article, I explained the difference between tables and views, using the analogy of a diner’s refrigerator and menu. As a follow-up, this article focuses on the types of views in SQL.

Broadly speaking, there are two types of views:

• Regular Views (View)
• Materialized Views (Materialized View)

A regular view is essentially a “saved SELECT statement.” It can be created from a single table or by joining multiple tables.
For clarity, this article introduces regular views in two forms: a simple example and a more complex example.

Regular Views (View)

A simple view extracts specific rows or columns from a single table. It’s useful when you want to hide unnecessary columns or reuse a particular condition.

Diner Analogy

Think of the refrigerator (the table) as containing meat, fish, vegetables, and condiments. A simple view is like creating a “salad ingredient list” on the menu that shows only the vegetables.

SQL Example

-- Refrigerator (fridge) table contents
-- id | category  | item
-- 1  | meat      | beef
-- 2  | veg       | lettuce
-- 3  | veg       | tomato
-- 4  | condiment | mayo

-- A view listing only salad ingredients
CREATE VIEW salad_ingredients AS
SELECT item
FROM fridge
WHERE category = 'veg';

👉 This means creating a menu entry (“salad ingredients”) that only shows the vegetables from the fridge, instead of listing all items.

Tip: More Complex Example (with JOINs across multiple tables)

Regular views are not limited to single tables—you can also join multiple tables or include aggregations to create a new shape of data (here we’ll call this a “complex view” for convenience). By defining such a view, you avoid having to write long JOIN or aggregation queries every time, making your queries much easier to reuse.

Diner Analogy

It’s like taking ingredients from the meat fridge (table A) and the vegetable fridge (table B), and combining them into a “cheeseburger set” on the menu.

SQL Example

-- Customer list (customers)
-- id | name
-- 1  | Alice
-- 2  | Bob

-- Orders list (orders)
-- id | customer_id | total_amount | ordered_at
-- 10 | 1           | 15.50        | 2025-09-12 10:03:00
-- 11 | 2           | 22.00        | 2025-09-12 11:10:00

-- Complex view: a receipt-style summary
CREATE VIEW order_receipts AS
SELECT
  o.id           AS order_id,
  c.name         AS customer_name,
  o.total_amount,
  o.ordered_at
FROM orders o
JOIN customers c ON o.customer_id = c.id;

👉 This combines the orders and customers tables into a single summarized view, similar to how a menu shows a finished dish instead of raw ingredients.

Materialized Views (Materialized View)

A materialized view saves the result of a SELECT query as actual data, allowing for faster access.
They are available in some RDBMSs such as PostgreSQL and Oracle, but not in MySQL or SQLite.

⚠️ One important point: REFRESH MATERIALIZED VIEW recomputes the entire view, which can be expensive for large datasets (refreshing may take significant time depending on the data size). Since incremental or automatic refresh is not part of standard SQL, it’s common to combine materialized views with application logic or a scheduler to manage updates.

Diner Analogy

If a regular view is like “cooking each order on demand,” then a materialized view is like a “pre-made bento box” with the following pros and cons:

• Ready to serve quickly (fast)
• But doesn’t include new ingredients automatically (no auto-refresh)
• Takes up fridge space (storage cost)
• Needs to be remade at appropriate times (maintenance cost)

SQL Example

-- Orders table (orders)

-- Precomputed daily sales (aggregation cache)
CREATE MATERIALIZED VIEW daily_sales AS
SELECT
  DATE(ordered_at) AS sales_date,
  SUM(total_amount) AS total_revenue,
  COUNT(*) AS orders_count
FROM orders
GROUP BY DATE(ordered_at);

-- Refresh manually after new data is added
REFRESH MATERIALIZED VIEW daily_sales;

👉 This saves the aggregated daily sales into a materialized view, making dashboards and reports much faster to load.

Summary

Type	Feature	Diner Analogy	Advantages	Disadvantages
Regular View (simple)	Extract from one table	Salad ingredients	Hide unnecessary columns	Limited functionality
Regular View (complex)	Join multiple tables	Cheeseburger set	Reuse complex queries	Query recalculated on every access
Materialized View	Save results as data	Pre-made bento	Fast, great for reports	No auto-refresh, storage cost, manual maintenance

Conclusion

SQL views fall into two main categories:

• Regular Views (View) → Always return up-to-date data

  • Simple examples: extracting columns or rows from a single table
  • Complex examples: joining multiple tables or including aggregations

• Materialized Views (Materialized View) → Store query results for faster performance (but require manual refresh)

Regular views are flexible for both simple and complex scenarios, while materialized views are particularly valuable for performance-sensitive use cases like reporting and dashboards.
Understanding the strengths and trade-offs of each type can help you design databases and applications more effectively.

As I mentioned in my previous article, I’ve recently been working more with Supabase at work, and I’m still experimenting and learning as I go. This article is part of my own study notes, and I hope it’s also useful for others who are just starting to learn about views.