A View in SQL is a virtual table created from a SELECT query. It shows data from real tables without storing rows itself. Use it to simplify queries, protect sensitive columns, and standardize business logic.
If you searched “update in ChatGPT5” or “what is view SQL”, this guide gives plain-English explanations plus copy-paste code.
Key Takeaways
- View = virtual table; shows results of a saved query.
- Tables store data; views present data (sometimes updatable).
- Use views for security, simplicity, and reusability.
- For heavy summaries, consider a materialized (indexed) view.
- Which is better? Neither. Use tables to store, views to show.
What Is a View in SQL? (Beginner Definition)
A SQL View is a named query you can select from like a table. It can pull columns from one or many tables, filter rows, or compute derived values—without duplicating data.
-- Create View (simple example)
CREATE VIEW employee_public AS
SELECT emp_id, full_name, department
FROM employees
WHERE status = 'Active';
Use it:
SELECT * FROM employee_public;
This returns only active employees and hides private columns such as salary or email.
Why Use Views? (Benefits with keywords)
- Simplicity: Hide complex JOINs and filters behind one name.
- Security: Expose only safe columns/rows; grant access to the view instead of base tables.
- Consistency: One definition → trusted single source of truth for reports.
- Maintainability: Change the view once; every report/query gets the update.
- Performance (situational): Fewer mistakes and less repeated logic; for precomputed speed use materialized/Indexed views.
Create, Update, Drop: View Syntax You’ll Actually Use
-- Create a view
CREATE VIEW sales_summary AS
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;
-- Update the definition of a view (replaces the query)
ALTER VIEW sales_summary AS
SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;
-- Remove a view
DROP VIEW sales_summary;
Make a view safer to modify data
CREATE VIEW it_staff AS
SELECT emp_id, department, salary
FROM employees
WHERE department = 'IT'
WITH CHECK OPTION; -- prevents updates that break the WHERE clause
Can We UPDATE a View in SQL?
Yes—if it’s a simple, updatable view that maps directly to one base table and avoids GROUP BY, DISTINCT, aggregates, and certain expressions.
-- Updatable view example
CREATE VIEW active_customers AS
SELECT customer_id, name, city
FROM customers
WHERE is_active = 1;
-- This updates the base table via the view
UPDATE active_customers
SET city = 'Austin'
WHERE customer_id = 42;
Usually NOT updatable when the view has: multi-table JOINs, aggregates, window functions, or computed columns that don’t map cleanly to base columns.
SQL View vs Table (Difference + Which Is Better)
| Feature | Table | View |
|---|---|---|
| Stores data | Yes (physical rows) | No (virtual; saved query) |
| Defined by | Columns & constraints | SELECT statement |
| Updatable | Yes (normal DML) | Sometimes (simple views) |
| Performance | Direct access | Re-runs underlying query |
| Security | Coarse | Fine-grained (expose only needed data) |
| Use case | Persist data | Present/secure/simplify data |
Which is better?
- Need to store and index data → Table.
- Need to reuse logic, hide columns, filter rows → View.
They complement each other.
View vs Materialized View (or Indexed View)
| Feature | Standard View | Materialized / Indexed View |
|---|---|---|
| Stores result | No | Yes (precomputed) |
| Speed | Runs query each time | Fast for repeated heavy reads |
| Freshness | Always current | May be stale until refreshed |
| Maintenance | Minimal | Needs storage + refresh/index strategy |
Use materialized views for dashboards and analytics that aggregate millions of rows.
Practical Patterns (Copy-Paste)
Secure sharing
CREATE VIEW order_public AS
SELECT order_id, order_date, customer_id, amount
FROM orders
WHERE status = 'Completed';
GRANT SELECT ON order_public TO analyst_role;
Join once, reuse everywhere
CREATE VIEW order_customer AS
SELECT o.order_id, o.order_date, o.amount,
c.customer_id, c.name, c.city
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;
Common Mistakes (and quick fixes)
- Deep view chains (View→View→View) → flatten or document dependencies.
- Assuming all views are updatable → keep “write” views simple; use
WITH CHECK OPTION. - Ignoring indexes on base tables → tune base tables first.
- Using views to hide bad schema design → fix the schema; use views for presentation.
FAQs
What is view in SQL with example?
A view is a virtual table defined by a query.
Example: CREATE VIEW employee_public AS SELECT emp_id, full_name FROM employees;
Can we update a view in SQL?
Yes, if it’s a simple view over one table without aggregates or GROUP BY. Otherwise it’s read-only.
SQL view vs table: which is better?
Neither. Tables store data; views present data. Choose based on purpose.
Difference between view and materialized view?
Views show live results; materialized views store precomputed results for speed and need refresh.
