Views: Saving Queries as Virtual Tables

A view is a saved query that behaves like a table — you can SELECT from it just like a real table, but under the hood it re-runs its underlying query every time.

Creating a view

CREATE VIEW employee_directory AS
SELECT e.first_name, e.last_name, e.job_title, d.name AS department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

Using a view

SELECT * FROM employee_directory WHERE department_name = 'Engineering';

Notice how much simpler this is than repeating the full join every time — the complexity is written once, in the view definition.

Why use views?

• Simplify complex queries — hide multi-table joins and calculations behind a simple name.
• Consistency — everyone querying "active customers" uses the exact same definition of "active."
• Security — you can grant access to a view that only exposes certain columns, without giving access to the full underlying table.

Views don't store data

A regular view has no data of its own — every time you query it, the underlying SELECT runs fresh against the live tables. If the base tables change, the view's results change immediately, with nothing to keep in sync manually.

Updating a view's definition

-- PostgreSQL / SQL Server
CREATE OR REPLACE VIEW employee_directory AS
SELECT e.first_name, e.last_name, d.name AS department_name, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.id;

MySQL and SQL Server before 2016 don't support CREATE OR REPLACE VIEW — you'd DROP VIEW then CREATE VIEW again.

Dropping a view

DROP VIEW employee_directory;

Materialized views (advanced, brief mention)

PostgreSQL and SQL Server also support materialized views — views that do store their results physically and need to be manually or automatically refreshed. These trade freshness for speed, and are typically used for expensive reports that don't need up-to-the-second data.

-- A ready-to-query practice database is loaded automatically before every run —
-- no CREATE TABLE needed. Available tables:
--   departments  (id, name, location)
--   employees    (id, first_name, last_name, email, department_id, job_title, salary, hire_date, manager_id)
--   customers    (id, customer_name, city, country, signup_date)
--   products     (id, product_name, category, price, stock_quantity)
--   orders       (id, customer_id, employee_id, order_date, status)
--   order_items  (id, order_id, product_id, quantity, unit_price)

SELECT * FROM employees WHERE department_id = 1;