Creating and Querying Views

Concept

A view is a named, stored SELECT statement. Querying a view runs its definition; the view itself stores no data. It is a virtual table, and to the rest of the SQL you write it behaves like a real one.

Syntax

CREATE VIEW view_name AS
SELECT ...;

CREATE OR REPLACE VIEW view_name AS      -- MySQL, MariaDB, PostgreSQL, Oracle
SELECT ...;

DROP VIEW view_name;
DROP VIEW IF EXISTS view_name;

Example

CREATE OR REPLACE VIEW v_employee_directory AS
SELECT e.id,
       CONCAT(e.first_name, ' ', e.last_name) AS full_name,
       e.email,
       d.name     AS department,
       d.location
FROM   employees e
LEFT JOIN departments d ON d.id = e.dept_id
WHERE  e.status = 'active';

-- Then use it exactly like a table
SELECT * FROM v_employee_directory WHERE location = 'Bengaluru';
SELECT department, COUNT(*) FROM v_employee_directory GROUP BY department;

-- A view over an aggregate: the report becomes a one liner
CREATE OR REPLACE VIEW v_order_summary AS
SELECT o.id            AS order_id,
       o.order_date,
       o.status,
       c.name          AS customer,
       COUNT(oi.product_id)             AS line_count,
       SUM(oi.quantity * oi.unit_price) AS line_value
FROM   orders o
JOIN   customers   c  ON c.id = o.customer_id
LEFT JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.order_date, o.status, c.name;

SELECT * FROM v_order_summary WHERE status = 'shipped' ORDER BY line_value DESC;

Explanation

When you query a view, the engine usually merges the view definition into your query and optimises the whole thing as one statement. So SELECT * FROM v_employee_directory WHERE location = 'Bengaluru' does not build the full directory and then filter it - the location predicate is pushed down into the underlying join.

Merging is not always possible. A view containing GROUP BY, DISTINCT, UNION, a window function or LIMIT must be materialised into a temporary result first, and only then can your outer filter apply. That is why filtering an aggregate view can be much slower than filtering a simple one.

What views are good for

-- Security: the view exposes what a role may see, the table stays private
CREATE VIEW v_public_employees AS
SELECT id, first_name, last_name, dept_id FROM employees;   -- no salary column

GRANT SELECT ON v_public_employees TO reporting_user;

Important rules

• A view stores no data. Every query against it runs the underlying SELECT.
• Every column in a view needs a name; alias every expression.
• A view is resolved at query time, so it always reflects current data.
• CREATE OR REPLACE VIEW cannot change the column list in some products - drop and recreate instead.
• Dropping a table that a view depends on leaves a broken view. MySQL only reports the error when the view is next queried.
• Views can be built on views, but each nesting level makes the plan harder to reason about.

Common mistakes

• Expecting a view to cache results. It does not - that is a materialised view, and MySQL has none.
• Nesting views four deep and then being unable to explain a slow query.
• Using SELECT * in a view definition: the column list is fixed when the view is created, so a later column added to the table does not appear, and a dropped one breaks the view.
• Assuming a view improves performance. It changes readability, not the work done.

Best practices

• Prefix view names consistently - v_ - so readers know it is not a table.
• List columns explicitly in the definition; never SELECT *.
• Keep nesting to one level where you can.
• Use views to publish a stable contract over a schema you expect to refactor.
• Where a heavy aggregate view is queried constantly, replace it with a scheduled summary table - the MySQL equivalent of a materialised view.

Practice

1. Create a view listing every customer with their order count and lifetime value.
2. Create a view exposing employees without the salary column, and explain how it helps with access control.
3. Why might SELECT * FROM v_order_summary WHERE status = 'shipped' be slower than the same filter on a simple view?