Creating and Using Views

By now you have written queries that join tables, filter with WHERE, and sort with ORDER BY. Those queries are powerful, but rewriting them every time is tedious. A monthly headcount report that joins employees to departments is fifteen lines long. Copying it into three dashboards means three places to update when the logic changes.

Worse, a complex query that touches sensitive columns must be copy-pasted by everyone who needs it. The salary column you meant to hide eventually leaks into a public dashboard because someone copied the full query and forgot to trim it.

A view is a named, reusable query. You define it once with CREATE VIEW, then select from it as if it were a table. It does not store data — it stores the question — so every time you query the view, the database re-runs that query against the latest data. Views simplify reporting by packaging complexity behind a name, and they restrict exposure by showing only the columns you choose.

flowchart LR
  E["employees table"] --> V["high_earners<br/>VIEW"]
  V --> Q["SELECT * FROM<br/>high_earners"]
  style V fill:#0e7490,color:#fff
  style Q fill:#1e293b,color:#fff

A view is a saved query that pulls from real tables and presents the result as if it were a table of its own.

Naming and dropping views

A view lives in the same namespace as tables, so you cannot create a view with the same name as an existing table. Most teams prefix views with v_ or suffix them with _view to make the intent obvious, though this is a convention rather than a rule. When a view is no longer needed, remove it with DROP VIEW view_name;. Dropping a view does not touch the underlying tables or the data inside them — it simply forgets the saved query.

The anatomy of CREATE VIEW

A view definition has the same shape as a query, wrapped in a CREATE VIEW header:

CREATE VIEW high_earners AS
SELECT name, salary
FROM employees
WHERE salary > 700000;

Read it as "create a view called high_earners that remembers this query." The keyword AS is required — it separates the name from the query body. After the view exists, you treat it like a table:

SELECT * FROM high_earners ORDER BY salary DESC;

The database does not store the result of the query; it stores the query text itself. Every SELECT from the view runs the underlying query fresh, which is why the data is always current. This also means you cannot create an index on a view — it has no stored rows to index. If you need indexed data, you build a table instead.

Create a view, then query it like a table.

CREATE VIEW high_earners AS
SELECT name, salary
FROM employees
WHERE salary > 700000;

SELECT * FROM high_earners ORDER BY salary DESC;

Alias every ambiguous column

When a view's query joins two tables that share a column name — both employees and departments have a name column — you must qualify or alias the column in the view definition. If you do not, SQLite raises an ambiguous-column error when the view is created, not when it is queried. The safest habit is to alias every selected column to a clear name: e.name AS employee_name. That makes the view's output predictable and self-documenting.

Views for simplification and security

A view can wrap a join, an aggregate, or a filter so that consumers do not need to know the underlying schema. A reporting tool can SELECT * FROM project_summary without knowing that the summary is built from three tables and a GROUP BY.

Views also restrict what is visible. If you want to give someone a list of employee names and roles without exposing salaries, create a view that omits the sensitive column. The underlying table still holds the salary, but the view simply does not select it. This is cheaper and more maintainable than copying data into a separate table.

flowchart LR
  E["employees"] --> V["public_employee_info<br/>VIEW"]
  D["departments"] --> V
  V --> R["name, role,<br/>department_name"]
  style V fill:#0e7490,color:#fff
  style R fill:#1e293b,color:#fff

A view can hide sensitive columns so consumers see only what they need.

A view that joins two tables and hides the salary column.

CREATE VIEW public_employee_info AS
SELECT e.name, e.role, d.name AS department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

SELECT * FROM public_employee_info ORDER BY name;

Views versus temporary tables

A view and a temporary table can look similar — both give you a simplified surface to query. The difference is fundamental. A temporary table stores actual rows on disk or in memory, which means it costs space and can become stale. A view stores only the query text, which means it costs almost nothing and is never stale. Choose a view when you want a live window onto existing data. Choose a table — or a temporary table — only when you need to snapshot data at a specific moment, or when you need to add indexes to speed up a report.

Views compose with everything you know

A view is queried exactly like a table, which means WHERE, ORDER BY, LIMIT, and even joins work on top of it. You can join a view to another table, or nest a view inside a more complex query. The database simply expands the view's definition into the larger query before it runs.

This composition is what makes views powerful: the view handles the hard join and aggregation, and the caller adds the final filter or sort. A project_summary view might group by department; a report query can then WHERE department_name = 'Engineering' on top of it without rewriting the GROUP BY. The caller only needs to know the view's column names, not the three tables it hides.

flowchart LR
  V["dept_summary<br/>VIEW"] --> F["WHERE headcount > 2"]
  F --> O["ORDER BY headcount DESC"]
  style V fill:#0e7490,color:#fff
  style O fill:#1e293b,color:#fff

Views compose: the view handles the aggregation, and the caller adds filtering and sorting on top.

A view that aggregates, then a query that filters on top of it.

CREATE VIEW dept_summary AS
SELECT d.name AS department_name, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.name;

SELECT * FROM dept_summary WHERE headcount > 2 ORDER BY headcount DESC;

Exercise

Create a view named high_earners that shows name and salary for every employee earning more than 700000.

CREATE VIEW high_earners AS
-- SELECT the name and salary of employees with salary > 700000
;

CREATE VIEW high_earners AS SELECT name, salary FROM employees WHERE salary > 700000;

Exercise

Create a view named project_details that shows each project's name (alias project_name) and its department's name (alias department_name). Join projects to departments.

CREATE VIEW project_details AS
-- join projects and departments, alias the two name columns
;

CREATE VIEW project_details AS SELECT p.name AS project_name, d.name AS department_name FROM projects p JOIN departments d ON p.department_id = d.id;

Exercise

This CREATE VIEW is broken — it selects name from both tables without qualifying which one, so SQLite complains about an ambiguous column. Fix it by qualifying the columns.

CREATE VIEW employee_dept AS
SELECT name, name
FROM employees e
JOIN departments d ON e.department_id = d.id
;

CREATE VIEW employee_dept AS SELECT e.name AS employee_name, d.name AS department_name FROM employees e JOIN departments d ON e.department_id = d.id;

Exercise

You query a view and see updated salary figures that were changed five minutes ago by another user. Why?

Exercise

Create a view named active_project_budgets that shows the name and budget of every project whose status is 'active'.

CREATE VIEW active_project_budgets AS
-- select name and budget for active projects
;

CREATE VIEW active_project_budgets AS SELECT name, budget FROM projects WHERE status = 'active';

Recap

CREATE VIEW name AS query saves a reusable query behind a table-like name.
Query a view with SELECT, WHERE, ORDER BY, LIMIT, and joins — exactly like a table.
A view does not store data; it re-runs its query each time, so results are always fresh.
Use views to simplify complex joins and to restrict which columns consumers can see.

Next you will learn how to change a table that already exists — adding columns, renaming tables, and planning safe schema evolution.

Naming and dropping views

The anatomy of CREATE VIEW

Alias every ambiguous column

Views for simplification and security

Views versus temporary tables

Views compose with everything you know

Recap

Checkpoint quiz

Go deeper — technical resources