|
Getting your Trinity Audio player ready...
|
When queries become long…
When joins become complex…
When reports repeat the same logic again and again…
You need Views.
A View is a virtual table created from a SELECT query.
It does NOT store data separately.
It stores the query logic.
You can think of it like:
A saved SQL query that behaves like a table.
Why Views Are Important
In real-life FMCG or MIS systems:
- Sales dashboard needs cleaned data
- Managers need summarized reports
- Finance team should not see salary columns
- BI tools connect to structured datasets
Instead of writing 50-line JOIN query every time…
You create a VIEW once.
Then simply:
SELECT * FROM sales_report_view;Clean. Professional. Scalable.
FMCG Dataset Tables (Used in Examples)
We’ll use:
- customers
- products
- employees
- sales
Basic View Syntax
PostgreSQL & MySQL
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;Simple View (Active Customers)
CREATE VIEW active_customers AS
SELECT
customer_id,
customer_name,
city,
phone
FROM customers
WHERE status = 'Active';Now instead of filtering every time:
SELECT * FROM active_customers;The filter is permanently saved inside the view.
View with JOIN
Real-world reporting example:
Sales report with customer & product details.
CREATE VIEW sales_detailed_view AS
SELECT
s.sale_id,
s.sale_date,
c.customer_name,
p.product_name,
s.quantity,
s.sale_amount
FROM sales s
JOIN customers c ON s.customer_id = c.customer_id
JOIN products p ON s.product_id = p.product_id;Now report becomes:
SELECT * FROM sales_detailed_view;Instead of writing 3-table JOIN every time.
This is how MIS dashboards are built.
Read More: Joins in SQL
View with Aggregation (Reporting View)
Let’s create monthly sales summary.
PostgreSQL
CREATE VIEW monthly_sales_summary AS
SELECT
DATE_TRUNC('month', sale_date) AS month,
SUM(sale_amount) AS total_sales
FROM sales
GROUP BY DATE_TRUNC('month', sale_date);MySQL
CREATE VIEW monthly_sales_summary AS
SELECT
DATE_FORMAT(sale_date, '%Y-%m-01') AS month,
SUM(sale_amount) AS total_sales
FROM sales
GROUP BY DATE_FORMAT(sale_date, '%Y-%m-01');Now managers can simply:
SELECT * FROM monthly_sales_summary;Professional reporting layer created.
Read More: Aggregate Functions in SQL
View for Data Cleaning
Instead of cleaning data every time, create clean view.
CREATE VIEW clean_sales_view AS
SELECT
sale_id,
COALESCE(quantity, 0) AS quantity,
CAST(sale_amount AS NUMERIC) AS sale_amount,
TRIM(UPPER(city)) AS city
FROM sales;Now all reports use clean data automatically.
This is real analyst workflow.
Also Read: Data Cleaning in SQL
Views for Security
Suppose employees table has:
- salary
- bank_account
- personal_email
You don’t want sales team to see salary.
Create restricted view:
CREATE VIEW employee_public_view AS
SELECT
employee_id,
employee_name,
department,
designation
FROM employees;Now give access to this view instead of full table.
This is enterprise-level design.
Updating Data Through Views
Important concept:
Can we UPDATE a view?
Simple Views → Yes
If:
- Single table
- No GROUP BY
- No aggregation
- No DISTINCT
Example:
UPDATE active_customers
SET city = 'Raipur'
WHERE customer_id = 5;This updates base table.
Complex Views → No
If view contains:
- JOIN
- GROUP BY
- SUM
- DISTINCT
Then updating is restricted.
PostgreSQL is stricter than MySQL here.
Read More: GROUP BY in SQL
Dropping a View
DROP VIEW view_name;Example:
DROP VIEW monthly_sales_summary;To avoid error:
DROP VIEW IF EXISTS monthly_sales_summary;Materialized View (PostgreSQL Only)
Normal view runs query every time.
Materialized View stores result physically.
PostgreSQL
CREATE MATERIALIZED VIEW sales_mv AS
SELECT
customer_id,
SUM(sale_amount) AS total_sales
FROM sales
GROUP BY customer_id;To refresh:
REFRESH MATERIALIZED VIEW sales_mv;⚠ MySQL does NOT support materialized views natively.
Materialized views improve performance for heavy reports.
PostgreSQL vs MySQL Differences
| Feature | PostgreSQL | MySQL |
|---|---|---|
| Basic Views | ✅ | ✅ |
| Updatable Views | Limited | More flexible |
| Materialized Views | ✅ | ❌ |
| Strict Validation | High | Moderate |
PostgreSQL is stronger for analytics systems.
Real MIS Example
Imagine management wants:
“Top 10 products by sales this year”
Create view:
CREATE VIEW top_products_view AS
SELECT
p.product_name,
SUM(s.sale_amount) AS total_sales
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY p.product_name
ORDER BY total_sales DESC;Now simply:
SELECT * FROM top_products_view LIMIT 10;This is dashboard-ready query.
Advantages of Views
✔ Simplifies complex queries
✔ Reusable logic
✔ Cleaner reporting
✔ Security control
✔ Cleaner dashboard integration
✔ Reduces human error
✔ Centralized business rules
Limitations of Views
❌ May reduce performance if very complex
❌ Not ideal for heavy aggregation (use materialized view in PostgreSQL)
❌ Hard to debug if layered too deeply
When Should You Use Views?
Use views when:
- Same query is used multiple times
- Creating reporting layer
- Providing restricted data access
- Cleaning data before BI tool connection
- Simplifying JOIN logic
Do NOT use view for:
- One-time query
- Extremely heavy transformation
How Companies Use Views
In real companies:
Raw Tables → Clean Views → Reporting Views → Dashboard
Example Flow:
sales_raw → clean_sales_view → monthly_sales_summary → Power BI
This layered architecture makes systems scalable.
Final Summary
Views in SQL are virtual tables created from SELECT queries that store logic, not data. They simplify complex joins, enable reusable reporting structures, improve security by hiding sensitive columns, and create clean reporting layers for dashboards. Both PostgreSQL and MySQL support views, but PostgreSQL also supports materialized views for performance optimization.
Views are essential in real-world MIS reporting systems, where data flows from raw tables to cleaned datasets and finally into summarized reports. Mastering views helps transform SQL queries into scalable, professional reporting systems.
What’s Next?
👉 CTE (Common Table Expressions)