Views

What this page covers

Introduction

What is it? A view is a saved SELECT query with a name. You query it like a table, but it stores no rows of its own. Every time you read from it, the database runs the saved query against the real tables.

Why do we need it? Two reasons come up again and again. First, security: the support team should see an employee's name and city, but not their salary or phone number. Second, convenience: a 5-table join that the finance team runs every morning should be written once, correctly, and reused, instead of being copy-pasted (with small mistakes) into 20 dashboards.

Real-world analogy A view is like a saved filter in your Gmail. "Unread from Swiggy" does not copy any emails into a new folder. It is just a stored search. Every time you click it, Gmail runs the search on your real inbox. A materialized view (a view whose result is saved as real rows, covered in section 8) is different: it is like exporting that search result to a spreadsheet. Fast to open, but stale until you export again.

Business use case An HR system in Bengaluru has an employees table with salary, phone, and PAN. The helpdesk tool needs employee names, departments, and office cities. Instead of giving the helpdesk database user access to the full table, the DBA creates a view with only the safe columns and grants SELECT on that view alone.

Dataset Used

CREATE TABLE departments (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE employees (
  emp_id    INT PRIMARY KEY AUTO_INCREMENT,
  full_name VARCHAR(100) NOT NULL,
  dept_id   INT NOT NULL,
  city      VARCHAR(50) NOT NULL,
  salary    DECIMAL(10,2) NOT NULL,
  phone     VARCHAR(15) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
) ENGINE=InnoDB;

INSERT INTO departments VALUES (10, 'Engineering'), (20, 'Sales'), (30, 'Support');

INSERT INTO employees (full_name, dept_id, city, salary, phone, is_active) VALUES
  ('Aarav Mehta',  10, 'Bengaluru', 2400000.00, '9876500001', 1),
  ('Priya Nair',   20, 'Mumbai',    1500000.00, '9876500002', 1),
  ('Rohit Sharma', 30, 'Delhi',      800000.00, '9876500003', 1),
  ('Sneha Iyer',   10, 'Bengaluru', 3100000.00, '9876500004', 0);

employees

Later sections also use an orders table: orders(order_id, customer_id, city, amount, status, created_at).

Beginner Core Path: View, Materialized View, or Table?

Start with the storage difference. The names sound similar, but the database does very different work.

A plain view is therefore not a materialized table and not a cache. A materialized view is closer to a table because its result rows occupy storage, but PostgreSQL still owns how it is populated and refreshed. MySQL does not provide materialized views, so its equivalent pattern is an explicitly managed summary table.

Run the smallest plain-view example first

This one-table example keeps only active employees and safe directory columns:

CREATE VIEW v_active_employees AS
SELECT emp_id, full_name, dept_id, city
FROM employees
WHERE is_active = 1;

SELECT emp_id, full_name, city
FROM v_active_employees
ORDER BY emp_id;

Step by step:

1. CREATE VIEW saves the SELECT; it does not copy employee rows.
2. Reading v_active_employees runs that saved filter against employees.
3. Sneha is absent because her is_active value is 0.
4. If the base row changes, the next view read changes too:

UPDATE employees SET is_active = 1 WHERE emp_id = 4;
SELECT emp_id, full_name FROM v_active_employees WHERE emp_id = 4;
-- Sneha now appears; no REFRESH was needed.

SHOW CREATE VIEW v_active_employees; -- MySQL: inspect the saved definition

A view can be used as a permission boundary only when grants and execution security are configured correctly. If the same user can read the base table directly, the view hides nothing. Section 3 explains definer/invoker behavior and safe grants.

The later join, reporting, updatable-view, security, and materialization examples build on this core model rather than replacing it.

Syntax

Mental Model

A view is a name for a query. It is not a copy of the data, and it is not a cache. If the base table changes, the next read from the view shows the change immediately.

1. What a View Is (and Is Not)

One thing to remember: in both MySQL and PostgreSQL, SELECT * inside a view is expanded to a fixed column list when you create the view. If you later add a column to the base table, the view does not show it until you recreate the view.

2. Creating, Replacing, and Dropping

CREATE VIEW v_employee_directory AS
SELECT e.emp_id,
       e.full_name,
       d.dept_name,
       e.city
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
WHERE e.is_active = 1;

Now query it like a table:

SELECT full_name, dept_name
FROM v_employee_directory
WHERE city = 'Bengaluru';

Sneha is not there because she is inactive. The view's own WHERE and your WHERE are combined.

To change the query, use CREATE OR REPLACE. Unlike DROP then CREATE, there is no moment when the view is missing and queries fail. In PostgreSQL it also keeps the grants on the view, which a drop would remove.

CREATE OR REPLACE VIEW v_employee_directory AS
SELECT e.emp_id, e.full_name, d.dept_name, e.city, e.dept_id
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
WHERE e.is_active = 1;

DROP VIEW IF EXISTS v_employee_directory;

PostgreSQL note: CREATE OR REPLACE VIEW there can only add new columns at the end. It cannot drop, rename, or change the type of existing columns. For those changes you have to drop and recreate.

3. Hiding Columns for Security

The helpdesk team needs names and cities, never salary or phone. Give them a view, not the table.

CREATE VIEW v_helpdesk_employees AS
SELECT emp_id, full_name, dept_id, city
FROM employees
WHERE is_active = 1;

CREATE USER 'helpdesk_app'@'%' IDENTIFIED BY 'change-me';
GRANT SELECT ON hr.v_helpdesk_employees TO 'helpdesk_app'@'%';
-- No grant on hr.employees at all

Now SELECT salary FROM employees from the helpdesk user fails with an access denied error, but the view works. The view hides columns (salary, phone) and rows (inactive employees).

How does this work if the user has no access to employees? In MySQL, a view runs with SQL SECURITY DEFINER by default: it uses the privileges of the person who created it. You can switch to SQL SECURITY INVOKER if you want the caller's own privileges checked instead. PostgreSQL views also run with the owner's privileges by default. Since PostgreSQL 15 you can set WITH (security_invoker = true).

For more on grants and roles, see Access Control.

A view is good for hiding columns, but it does not cover every row-level security need. If you need "each sales rep sees only their own customers", PostgreSQL's row-level security policies are a better tool. MySQL has no built-in equivalent, so teams do it in the app, or with a view that filters on the logged-in user. Be careful there: inside a SQL SECURITY DEFINER view, CURRENT_USER() returns the view's definer, not the caller. Use USER(), which returns the account the client logged in with. And if every request comes through one shared app user, the database cannot tell reps apart at all, so the filter has to live in the app.

4. Simplifying Joins for Reporting

The finance team at a Delhi D2C brand wants daily sales by city every morning. Without a view, every analyst writes their own join and their own rules for "which orders count". One forgets to exclude cancelled orders and the numbers do not match.

CREATE OR REPLACE VIEW v_daily_city_sales AS
SELECT DATE(o.created_at) AS sale_date,
       o.city,
       COUNT(*)            AS orders,
       SUM(o.amount)       AS revenue
FROM orders o
WHERE o.status IN ('DELIVERED', 'SHIPPED')
GROUP BY DATE(o.created_at), o.city;

SELECT city, revenue
FROM v_daily_city_sales
WHERE sale_date = '2026-10-08'
ORDER BY revenue DESC;

The rule "only DELIVERED and SHIPPED count as sales" now lives in one place. Change it once and every dashboard follows.

5. Updatable Views

Some views let you run INSERT, UPDATE, and DELETE through them, and the change goes to the base table. The rule of thumb: the database must be able to map each row in the view to exactly one row in one base table.

UPDATE v_helpdesk_employees
SET city = 'Pune'
WHERE emp_id = 3;
-- Changes employees.city for Rohit

A view is not updatable in MySQL if its query has any of these:

Computed columns are a partial case. You cannot write to a column like salary * 12, but the other plain columns of the same view can still be updated.

More rules:

• INSERT through a view works only if the view includes every base column that has no default value. v_helpdesk_employees is missing salary and phone (both NOT NULL, no default), so an insert through it fails.
• For a view with a join, MySQL lets an UPDATE change columns of only one base table per statement. DELETE through a join view is not allowed.
• PostgreSQL makes simple single-table views updatable automatically. For anything more complex you write an INSTEAD OF trigger on the view.

My advice: use views for reads and write to the base tables directly. Updatable views work, but the rules above are easy to trip over, and the next developer will not expect an UPDATE on something named v_... to change a real table.

6. WITH CHECK OPTION

Take a view that shows only Bengaluru staff. Someone updates through it and sets city = 'Chennai'. Without a check, the update succeeds and the row vanishes from the view straight away. The person who made the change can no longer see or fix it. An insert through the view can do the same thing: add a row the view itself will never show.

CREATE OR REPLACE VIEW v_bengaluru_staff AS
SELECT emp_id, full_name, dept_id, city, salary, phone
FROM employees
WHERE city = 'Bengaluru'
WITH CHECK OPTION;

UPDATE v_bengaluru_staff SET city = 'Chennai' WHERE emp_id = 1;
-- ERROR 1369 (HY000): CHECK OPTION failed 'hr.v_bengaluru_staff'

WITH CHECK OPTION says: any row you write through this view must still match the view's WHERE afterwards. It is useful when a view is the only door a team has to a table, for example a regional manager who should only manage staff in their own city.

LOCAL vs CASCADED: when a view is built on another view, CASCADED (the MySQL default) checks the conditions of all views underneath too. LOCAL checks only this view's own condition, plus any underlying views that have their own check option.

7. Performance: A View Is Not a Cache

Reading from a view runs its query every time. A slow query inside a view is still a slow query. A view on top of a view on top of a view is a common way to end up with a 2-second dashboard nobody can explain.

MySQL processes a view in one of two ways:

MySQL has to use TEMPTABLE when the view has GROUP BY, HAVING, DISTINCT, aggregates, UNION, LIMIT, and a few other things that cannot be merged. The default ALGORITHM = UNDEFINED lets MySQL choose, and it prefers MERGE when it can. If you ask for MERGE on a view that cannot be merged, MySQL gives a warning and quietly falls back.

Since MySQL 8.0.22 the optimizer can sometimes push your outer WHERE down into a TEMPTABLE view before it is computed (for example a filter on a GROUP BY column). That helps, but check EXPLAIN rather than assume it happened.

CREATE ALGORITHM = MERGE VIEW v_active_employees AS
SELECT emp_id, full_name, city FROM employees WHERE is_active = 1;

Always run EXPLAIN on a query that uses a view, the same way you would for a normal query. See Performance and Debugging. If you see <derived2> in the table column, the view was materialized into a temp table.

PostgreSQL does not have these keywords. Its planner inlines the view query into your query in most cases.

8. Materialized Views

A materialized view stores the result as real rows on disk. Reads are fast because nothing is computed at read time. The cost: the data is only as fresh as the last refresh.

8.1 PostgreSQL

CREATE MATERIALIZED VIEW mv_daily_city_sales AS
SELECT DATE(created_at) AS sale_date,
       city,
       COUNT(*)    AS orders,
       SUM(amount) AS revenue
FROM orders
WHERE status IN ('DELIVERED', 'SHIPPED')
GROUP BY DATE(created_at), city;

CREATE UNIQUE INDEX ux_mv_daily_city_sales ON mv_daily_city_sales (sale_date, city);

-- Recompute everything. Blocks reads on the view while it runs.
REFRESH MATERIALIZED VIEW mv_daily_city_sales;

-- Recompute without blocking readers. Needs a unique index on the view.
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_city_sales;

Points to remember:

• A refresh always recomputes the full query. PostgreSQL has no built-in incremental refresh.
• CONCURRENTLY keeps the old data readable while the new data is computed, then applies only the differences. It is slower to run but users never wait. It fails without a unique index.
• Schedule the refresh with pg_cron, a cron job, or your app scheduler.

8.2 MySQL: build it yourself

MySQL has no materialized views. The common pattern is a summary table plus a scheduled job.

CREATE TABLE daily_city_sales (
  sale_date DATE NOT NULL,
  city      VARCHAR(50) NOT NULL,
  orders    INT NOT NULL,
  revenue   DECIMAL(14,2) NOT NULL,
  PRIMARY KEY (sale_date, city)
) ENGINE=InnoDB;

CREATE EVENT ev_refresh_daily_city_sales
ON SCHEDULE EVERY 15 MINUTE
DO
  INSERT INTO daily_city_sales (sale_date, city, orders, revenue)
  SELECT DATE(created_at), city, COUNT(*), SUM(amount)
  FROM orders
  WHERE status IN ('DELIVERED', 'SHIPPED')
    AND created_at >= CURDATE() - INTERVAL 1 DAY
  GROUP BY DATE(created_at), city
  ON DUPLICATE KEY UPDATE
    orders  = VALUES(orders),
    revenue = VALUES(revenue);

This only recomputes yesterday and today, so it stays cheap even when orders has 50 crore rows. The event scheduler must be enabled (event_scheduler = ON); verify the server setting instead of assuming it is enabled in a particular MySQL installation. Many teams prefer an external scheduler (Airflow, a Kubernetes CronJob) so the job shows up in their normal monitoring and alerting.

Two things this simple version gets wrong, and you should decide if they matter:

• An order from last week that gets cancelled today is outside the window, so last week's numbers never change. A nightly job that rebuilds the last 30 days usually covers it.
• If every order for a city on a day gets cancelled, the old summary row stays, because this job only inserts and updates. The nightly rebuild should delete and reinsert its date range.

(MySQL 8.0.20 and later mark VALUES() in ON DUPLICATE KEY UPDATE as deprecated, but it still works. The newer INSERT ... VALUES (...) AS new row alias only applies to INSERT ... VALUES. For INSERT ... SELECT with GROUP BY, the replacement is to wrap the SELECT in a derived table, for example SELECT * FROM (...) AS s, and write orders = s.orders in the update part.)

9. When to Use Which

Deep Dive

Dependencies break silently. If you drop or rename a column that a view uses, MySQL lets you do it, and the view breaks only when someone queries it (ERROR 1356: View references invalid table(s) or column(s)). PostgreSQL is stricter: it refuses to drop a column a view depends on unless you use CASCADE, which drops the view too. Run CHECK TABLE v_name in MySQL after migrations to catch broken views.

ORDER BY inside a view. Do not rely on it. The outer query decides the final order. Put ORDER BY in the query that reads from the view.

Views and stored procedures. Both keep logic in the database. A view is a reusable SELECT. A procedure can run many statements with control flow. See Stored Procedures.

Indexing a materialized view. In PostgreSQL you can add normal indexes to a materialized view, because its rows are stored on disk like a table. That is a big reason they are fast.

Refresh cost in production. A refresh runs the full query, so a 2-minute query refreshed every 5 minutes keeps the database busy 40 percent of the time. CONCURRENTLY is also heavier than a plain refresh, because it compares the old and new results row by row. Measure the refresh time before you pick the schedule, and alert when a refresh fails, otherwise the dashboard quietly shows yesterday's numbers.

Real-world Use Cases

• HR and payroll: a view without salary and PAN for the helpdesk and IT teams.
• Reporting: v_daily_city_sales as the single source of truth for "what counts as revenue".
• Legacy migrations: when a table is split in two, a view with the old table name keeps old reports working while they are moved.
• Analytics dashboards: a PostgreSQL materialized view refreshed every 10 minutes for a Metabase dashboard on Big Billion Day.
• MySQL rollups: summary tables updated by an event or a cron job every 15 minutes.

Review Tips

• Say "a view is a stored query, not stored data" in the first sentence.
• Know the two main uses: security (hide columns and rows) and simplicity (reuse joins and business rules).
• Know that MySQL has no materialized views and what teams do instead.
• Mention MERGE vs TEMPTABLE if the interviewer asks about view performance.
• In an interview, "use a view for security" is a fine answer. In a real system, also check who owns the view, whether it runs as definer or invoker, and that nobody still has a direct grant on the base table.

Common Mistakes to Avoid

Mistake 1: Expecting a view to make a query faster

It runs the same query every time. Fix indexes or use a materialized view or summary table.

Mistake 2: Stacking views many levels deep

Each layer hides more logic. Joins get repeated and the plan gets hard to read. Keep it to one or two levels.

Mistake 3: Using SELECT * in a view and expecting new columns to show up

The column list is frozen when the view is created. Recreate the view after adding columns.

Mistake 4: Granting the base table and the view

Then the view hides nothing. Grant only the view.

Mistake 5: Updating through a view without WITH CHECK OPTION

Rows can move out of the view, or rows can be inserted that the view's users will never see.

Mistake 6: Using REFRESH MATERIALIZED VIEW without CONCURRENTLY on a busy dashboard

Readers are blocked until the refresh finishes. Add a unique index and use CONCURRENTLY.

Interview Questions

"What is a view, and does it store data?"

A view is a saved SELECT query with a name. It stores no rows, so every read runs the query against the base tables and always shows current data.

"How can a view help with security?"

You can expose only safe columns and rows through the view and grant SELECT on the view only, not on the base table. The user can query names and cities but never sees salary or phone.

"When is a view not updatable?"

When a view row does not map to exactly one base table row, for example with GROUP BY, aggregates, DISTINCT, UNION, or a TEMPTABLE algorithm. Computed columns also cannot be written.

"What does WITH CHECK OPTION do?"

It rejects any INSERT or UPDATE through the view that would produce a row the view's WHERE clause does not match. It stops rows from silently disappearing from the view.

"What is the difference between a view and a materialized view?"

A view runs its query on every read and is always fresh. A materialized view stores the result as real rows, so reads are fast, but the data is only as fresh as the last refresh.

"MySQL has no materialized views. What do you do instead?"

Create a summary table and refresh it on a schedule with a MySQL event or an external job, using INSERT ... ON DUPLICATE KEY UPDATE over a recent time window.

Key Takeaways

• A view is a named query. It stores no data and is always as fresh as the base tables.
• Use views to hide columns and rows, and to keep one shared definition of a join or a business rule.
• CREATE OR REPLACE VIEW swaps the query with no gap. DROP VIEW never touches base tables.
• Simple single-table views can be updatable. Aggregates, DISTINCT, UNION, and TEMPTABLE make a view read-only.
• WITH CHECK OPTION blocks writes that would fall outside the view.
• A view is not a cache. In MySQL, MERGE views can use base table indexes, TEMPTABLE views often cannot.
• PostgreSQL has materialized views with REFRESH MATERIALIZED VIEW [CONCURRENTLY]. MySQL needs a summary table plus a scheduled job.