Simple Views

What Is a Simple View?

A view is a named, stored query that behaves like a virtual table. A simple view is the most basic kind: it draws from a single base table, has no joins, no aggregation, no GROUP BY, and no DISTINCT. It is essentially a saved SELECT statement wearing a table's clothes.

sql
CREATE VIEW high_earners AS
SELECT id, name, salary
FROM employees
WHERE salary > 80000;

Once created, you query the view exactly like a table:

sql
SELECT * FROM high_earners
ORDER BY salary DESC;

The database re-executes the underlying SELECT against employees every time high_earners is queried (unless it has been materialized — see 19.6). No data is duplicated; the view is just a lens on employees.

Why Bother?

• Readability — SELECT * FROM high_earners is clearer than repeating the WHERE salary > 80000 predicate in every application query.
• Reuse — the filter logic lives in one place. If "high earner" is redefined as salary > 90000, you change the view once instead of hunting down every copy of the query.
• Abstraction — application code and analysts can depend on a stable name (high_earners) even if the underlying table's structure evolves slightly (see 19.7).
• A stepping stone to security — a simple view that selects only some columns is the classic way to hide sensitive data (see 19.4).

Column Subsetting

A simple view can also just narrow columns, with no filter at all:

sql
CREATE VIEW employee_directory AS
SELECT id, name, department
FROM employees;

This exposes id, name, and department while completely hiding salary from anyone who only has access to employee_directory.

Edge Cases

• The view has no storage of its own. SELECT * FROM high_earners and SELECT id, name, salary FROM employees WHERE salary > 80000 produce identical query plans in most optimizers (the view is "inlined").
• Simple views are (usually) updatable. Because there's a one-to-one mapping between a view row and an underlying table row, INSERT/UPDATE/DELETE through high_earners is normally allowed — details in 19.5.
• A row can silently disappear from view results when it's updated to no longer satisfy the view's WHERE clause (e.g., someone's salary is updated below 80000 through the view itself) — this is sometimes called the view "losing" a row it just inserted, unless WITH CHECK OPTION is used to forbid it.
• Naming collisions: a view cannot share a name with an existing table or view in the same schema.

Key Takeaways / Interview Q&A

Q: Does a simple view store data? A: No — it stores only the query definition. Data is fetched fresh from employees on every access.

Q: What makes a view "simple" rather than "complex"? A: Single base table, no joins, no aggregate functions, no GROUP BY/DISTINCT — a straightforward projection/selection.

Q: If I `ALTER TABLE employees ADD COLUMN bonus`, does `high_earners` automatically show it? A: No — SELECT id, name, salary is an explicit column list, so bonus won't appear unless the view is redefined with CREATE OR REPLACE VIEW. (A view defined with SELECT * would pick up new columns automatically in most engines, which is one reason explicit column lists are recommended.)