++++

Data Engineering

May 2026×8 min read

Create reusable saved queries with MySQL views, query them like tables, and update or drop them safely.

17. Views

Driptanil DattaSoftware Developer

Views

A view is a saved query that you can query like a table. It does not usually store data itself; it stores the SQL definition.

Create a view

CREATE VIEW paid_order_summary AS
SELECT
  customer_id,
  COUNT(*) AS paid_order_count,
  SUM(total_amount) AS paid_total
FROM orders
WHERE status = "paid"
GROUP BY customer_id;

Now query the view:

SELECT *
FROM paid_order_summary
ORDER BY paid_total DESC;

Views with joins

CREATE VIEW customer_order_details AS
SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  o.order_id,
  o.order_date,
  o.status,
  o.total_amount
FROM customers AS c
INNER JOIN orders AS o
  ON c.customer_id = o.customer_id;

Use the view:

SELECT *
FROM customer_order_details
WHERE status = "paid";

Replace a view

Use CREATE OR REPLACE VIEW when changing the definition.

CREATE OR REPLACE VIEW paid_order_summary AS
SELECT
  customer_id,
  COUNT(*) AS paid_order_count,
  SUM(total_amount) AS paid_total,
  AVG(total_amount) AS average_paid_order
FROM orders
WHERE status = "paid"
GROUP BY customer_id;

Drop a view

DROP VIEW paid_order_summary;

When to use views

To reuse common reporting logic.
To hide complex joins from beginners or dashboards.
To expose only selected columns.
To keep business definitions consistent.

Views are not a replacement for well-designed tables, but they are useful for making repeated queries easier to maintain.