SQL Views

A view is a stored, named SQL query that behaves like a virtual table: you can SELECT from it just like a real table, but it has no data of its own — every time it is queried, the database runs the underlying query fresh against the real, underlying tables. Views are a way to package a complex or frequently repeated query behind a simple, reusable name.

Why it matters

A view is a saved query that can be used like a table. It lets a complicated join be written once and reused by name, and it lets you give someone access to part of a table, hiding columns such as password hashes or salaries. Reporting tools and permission schemes rely on views heavily.

Creating and Using a View

CREATE VIEW view_name AS SELECT ... defines a view. Once created, it can be queried exactly like a table: SELECT * FROM view_name. Because a view is just a saved query definition, it always reflects the current state of the underlying tables — it is never "stale" the way a manually copied table would be.

Simplifying Complex Queries

A view is especially useful when several parts of an application repeatedly need the same complicated join or aggregation. Instead of duplicating a ten-line query with multiple joins in every report and every application module, that logic is written once inside a view, and every consumer simply selects from the view by name — improving both consistency and maintainability.

Restricting Access with Views

A view can also expose only a subset of columns or rows from an underlying table, which is a common way to restrict what certain users or applications can see. For example, a view over an employees table might omit the salary column entirely, so that users granted access only to the view can never see compensation data, even though the full data still exists in the underlying table.

Example

CREATE VIEW employee_directory AS
SELECT e.id, e.name, e.department, d.location
FROM employees e
JOIN departments d ON d.name = e.department;

SELECT * FROM employee_directory WHERE department = 'Engineering';

Returns 2 rows: (1, 'Asha Rao', 'Engineering', 'Bangalore'), (3, 'Meera Iyer', 'Engineering', 'Bangalore') — salary is never exposed, since it is not selected in the view.

Exercise

Create a view named active_users that shows only the id and name of users whose active flag is true, leaving out the password column. Then select the names from the view.

Expected output: name then Asha

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, password_hash TEXT, active BOOLEAN);
INSERT INTO users (id, name, password_hash, active) VALUES
  (1, 'Asha', 'x9f2', TRUE),
  (2, 'Ravi', 'k3p8', FALSE);

-- TODO: create the view active_users (id and name of active users)

-- TODO: select the names from the view

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, password_hash TEXT, active BOOLEAN);
INSERT INTO users (id, name, password_hash, active) VALUES
  (1, 'Asha', 'x9f2', TRUE),
  (2, 'Ravi', 'k3p8', FALSE);

CREATE VIEW active_users AS
SELECT id, name
FROM users
WHERE active = TRUE;

SELECT name FROM active_users;

Quick quiz

Interview questions