SQL Notes
Learn how to create views in SQL to build virtual tables from queries, simplify complex joins, restrict data access, and provide logical data independence.
Imagine you have a complex query that joins five tables, applies several filters, and calculates aggregated values. You use this query in ten different places across your application. If the table structure changes, you need to update all ten places. Views solve this problem elegantly.
A view is a virtual table defined by a SQL query. It does not store data itself — every time you query a view, the database runs the underlying query and returns fresh results. You create it once, and then use it like a regular table in all your SELECT statements.
Think of a view as a saved query with a name. Instead of copying and pasting the same complex query across different parts of your application, you save it as a view and then reference the view name wherever you need that data. The beauty is that if the underlying logic needs to change, you update the view definition in one place, and every part of your application that uses the view automatically gets the updated logic. This is the principle of DRY (Don't Repeat Yourself) applied at the database level.
Basic Syntax
CREATE VIEW view_name AS
SELECT columns
FROM tables
WHERE conditions;Once created, you query it like a table:
SELECT * FROM view_name;
SELECT column1, column2 FROM view_name WHERE condition;Simple View Example
-- Create a view showing only active employees
CREATE VIEW ActiveEmployees AS
SELECT EmpID, Name, Email, Department, Salary
FROM Employees
WHERE Status = 'Active';Now instead of writing the WHERE clause every time:
-- Before: Repeating the filter everywhere
SELECT Name, Department FROM Employees WHERE Status = 'Active';
-- After: Clean and simple
SELECT Name, Department FROM ActiveEmployees;Views with JOINs
Views really shine when they encapsulate complex joins:
In real-world applications, most meaningful queries involve joining multiple tables. Customer orders require joining Customers, Orders, and OrderItems. Student transcripts require joining Students, Enrollments, and Courses. These multi-table queries are long, complex, and error-prone to write repeatedly. A view wraps this complexity into a simple name, so application developers can focus on filtering and presenting data rather than reconstructing join logic every time they need related information.
-- Without a view: Complex query repeated everywhere
SELECT
o.OrderID,
c.Name AS CustomerName,
c.Email,
o.OrderDate,
o.TotalAmount,
o.Status
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID;-- Create a view to encapsulate this
CREATE VIEW OrderDetails AS
SELECT
o.OrderID,
c.Name AS CustomerName,
c.Email AS CustomerEmail,
c.City,
o.OrderDate,
o.TotalAmount,
o.Status
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID;Now everyone queries the simple view:
-- All pending orders
SELECT * FROM OrderDetails WHERE Status = 'Pending';
-- Orders from Mumbai customers
SELECT OrderID, CustomerName, TotalAmount FROM OrderDetails WHERE City = 'Mumbai';
-- Recent large orders
SELECT * FROM OrderDetails WHERE TotalAmount > 10000 AND OrderDate >= '2026-01-01';Views with Aggregation
CREATE VIEW DepartmentStats AS
SELECT
Department,
COUNT(*) AS EmployeeCount,
AVG(Salary) AS AvgSalary,
MAX(Salary) AS MaxSalary,
MIN(Salary) AS MinSalary,
SUM(Salary) AS TotalPayroll
FROM Employees
WHERE Status = 'Active'
GROUP BY Department;-- Simple query on the aggregated view
SELECT * FROM DepartmentStats WHERE EmployeeCount > 10 ORDER BY AvgSalary DESC;Views for Security
Views are commonly used to restrict what data different users can see:
Database security goes beyond just authentication. Even after a user connects to the database, you need to control which columns and rows they can access. A payroll clerk needs salary data but should not see medical records. A customer service representative needs contact information but not financial details. Views implement this column-level and row-level security elegantly. You grant each user role access only to specific views rather than to the underlying tables, ensuring they can never see data outside their authorized scope.
-- HR can see everything
CREATE VIEW HR_EmployeeView AS
SELECT EmpID, Name, Email, Phone, Salary, BankAccount, AadharNumber, Department
FROM Employees;
-- Regular managers see limited info (no salary, no personal documents)
CREATE VIEW Manager_EmployeeView AS
SELECT EmpID, Name, Email, Department, JoinDate, Status
FROM Employees;
-- Public directory (minimal info)
CREATE VIEW PublicDirectory AS
SELECT Name, Department, Email
FROM Employees
WHERE Status = 'Active';Grant appropriate permissions:
GRANT SELECT ON HR_EmployeeView TO hr_team;
GRANT SELECT ON Manager_EmployeeView TO managers;
GRANT SELECT ON PublicDirectory TO all_staff;Real-World Example: E-Commerce Dashboard
Dashboard views are perhaps the most common use case in production databases. Business stakeholders need real-time metrics — daily revenue, order counts, conversion rates — but they cannot write SQL. By creating views that pre-define the aggregation logic, you give them simple tables they can query with basic SELECT statements or connect to business intelligence tools like Tableau, Power BI, or Metabase. The complexity lives in the view definition, not in every downstream consumer.
CREATE VIEW SalesDashboard AS
SELECT
DATE(o.OrderDate) AS SaleDate,
COUNT(o.OrderID) AS TotalOrders,
SUM(o.TotalAmount) AS DailyRevenue,
AVG(o.TotalAmount) AS AvgOrderValue,
COUNT(DISTINCT o.CustomerID) AS UniqueCustomers
FROM Orders o
WHERE o.Status != 'Cancelled'
GROUP BY DATE(o.OrderDate);-- Quick dashboard query
SELECT * FROM SalesDashboard WHERE SaleDate >= '2026-06-01' ORDER BY SaleDate;
-- Compare today vs yesterday
SELECT * FROM SalesDashboard WHERE SaleDate >= CURRENT_DATE - INTERVAL 2 DAY;Real-World Example: Student Transcript View
CREATE VIEW StudentTranscript AS
SELECT
s.StudentID,
CONCAT(s.FirstName, ' ', s.LastName) AS StudentName,
s.Email,
c.CourseCode,
c.CourseName,
c.Credits,
e.Grade,
e.EnrollDate
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID
ORDER BY s.StudentID, e.EnrollDate;-- Get a specific student's transcript
SELECT CourseName, Credits, Grade FROM StudentTranscript WHERE StudentID = 101;
-- Find all students who got A+
SELECT StudentName, CourseName FROM StudentTranscript WHERE Grade = 'A+';Views with Column Aliases
You can rename columns in the view definition:
CREATE VIEW EmployeeSummary (ID, FullName, Dept, MonthlySalary) AS
SELECT
EmpID,
CONCAT(FirstName, ' ', LastName),
Department,
Salary / 12
FROM Employees;Creating Views with OR REPLACE
To avoid errors if the view already exists:
CREATE OR REPLACE VIEW ActiveEmployees AS
SELECT EmpID, Name, Email, Department, Salary, JoinDate
FROM Employees
WHERE Status = 'Active';This creates the view if it does not exist, or updates its definition if it does.
Checking View Definitions
MySQL
SHOW CREATE VIEW ActiveEmployees;PostgreSQL
SELECT definition FROM pg_views WHERE viewname = 'activeemployees';SQL Server
EXEC sp_helptext 'ActiveEmployees';Listing All Views
MySQL
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'your_database';PostgreSQL
SELECT viewname FROM pg_views WHERE schemaname = 'public';Common Mistakes to Avoid
Mistake 1: Selecting star in views. If the underlying table adds columns, SELECT * in a view can cause issues. List columns explicitly.
Mistake 2: Creating views that are too complex. A view joining 10 tables with multiple subqueries will be slow every time it is queried. Consider materialized views for complex aggregations.
Mistake 3: Not considering performance. Views do not cache results. Every query against the view re-executes the underlying SQL.
Mistake 4: Nested views (views on views on views). Deeply nested views become impossible to debug and may have poor performance.
Best Practices
- Name views clearly — prefix with
vw_or use descriptive names likeActiveEmployees - List columns explicitly instead of using SELECT *
- Document the purpose of each view with comments
- Use OR REPLACE to update views without dropping them
- Avoid deep nesting — views referencing other views should be at most 2 levels deep
- Use views for security to restrict column access for different roles
Views are one of SQL's most elegant features. They provide abstraction, security, and simplification all at once. Once you start using them, you will wonder how you ever managed without them.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for CREATE VIEW in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, views, create, view
Related SQL Complete Guide Topics