Database Views: A Saved Query That Acts Like a Table

WHY IT EXISTS Databases often contain complex relationships and sensitive data. A view solves two problems: first, it simplifies access by hiding complex joins and calculations behind a simple name. Second, it enhances security by exposing only a subset of data to certain users, preventing them from seeing columns or rows they shouldn't.

THE MENTAL MODEL A database view is like a saved search filter in your email client. The filter itself doesn't store copies of your emails; it just stores the search criteria. When you click the filter (e.g., "From: Boss"), your email client runs a live search against your full inbox and shows you the matching results. The view is the saved query; the underlying tables are your live inbox.

HOW IT WORKS When you create a view, the database stores its SELECT statement in the data dictionary. It does not store the resulting data. When you run a query against the view, like SELECT * FROM v_active_users, the database engine replaces the view's name with its stored SELECT statement. It then executes this expanded query against the actual base tables and returns the results, just as if the view were a physical table.

WHEN TO USE IT Use views to encapsulate business logic, providing a consistent definition of concepts like an "active user" across multiple applications. They are also excellent for creating a stable API for developers; you can refactor the underlying tables, and as long as the view's output remains the same, client applications won't break. Finally, use them for security to grant users access to a view of the data rather than the sensitive underlying tables.

WHEN NOT TO USE IT Avoid views for performance-critical queries if the underlying query is complex. Since a standard view re-executes its query on every access, it can become a major performance bottleneck. In these high-frequency read scenarios, a materialized view, which physically stores the results and is refreshed periodically, is often a better choice. Also, updating data through complex views (e.g., those with joins) can be restricted or have unpredictable side effects.

ONE CANONICAL EXAMPLE Imagine a users table with PII and an orders table. To create a public sales dashboard, you don't want to expose customer names or addresses. You can create a v_public_sales_summary view that joins the tables, groups by product category, and sums the sales amounts, exposing only the category and total sales. Analysts can query this safe, simple view without ever touching the sensitive base tables.