An SQL View is like a virtual table. It does not store data itself but shows data stored in other tables. Think of it as a saved query that you can reuse anytime. Views help you simplify complex queries, improve security, and make databases easier to work with.
Now you can query employee_details like a table:
This query will then return all the first_name, last_name and job_title rows with department_id value 1 from the employees table.
Practice: Create a view and then return the first_name and last_name of the employee with department_id of 3.
Use Cases of SQL Views
- Security: Hide sensitive columns (like salary or passwords) from users.
- Simplification: Save complex joins or filters as a view, so users just query the view instead of writing long SQL every time.
- Abstraction: Provide a layer between users and raw tables, so if the table changes, you only update the view.
- Reusability: Use the same view in multiple queries instead of rewriting code.
Creating, Using, and Dropping Views
Creating a View
Using a View
Dropping a View
Updatable vs Non-Updatable Views
Updatable Views in SQL
You can use INSERT, UPDATE, or DELETE on them. Usually possible when the view is based on a single table without complex joins or aggregations.
Non-Updatable Views in SQL
If the view contains joins, aggregate functions (SUM, AVG), DISTINCT, or GROUP BY, then it becomes read-only.
FAQs
1. Do SQL Views store data permanently?
No. Views are virtual tables that only store the query definition, not the actual data. The data shown by a view always comes from the underlying base tables.
2. What is the difference between a View and a Table in SQL?
- A table physically stores data.
- A view is just a saved query that displays data from one or more tables.
Views depend on tables, if the underlying table changes, the view’s results also change.
3. Can SQL Views improve performance?
Views themselves do not guarantee performance improvement. However, they can simplify queries and reduce errors. In some databases, indexed views can boost performance because they store precomputed results.
4. Can we insert, update, or delete data through a View?
Yes, but only in updatable views (based on a single table without aggregates or joins). If a view includes GROUP BY, DISTINCT, or complex joins, it becomes read-only.
5. What happens if the underlying table of a View is dropped?
If a base table is dropped, the view becomes invalid and will throw an error when queried. The view must be recreated or updated once the missing table is restored.