SQL Views
This tutorial will explain how to create Views in SQL and how to use them with the examples.
SELECT statement is used to retrieve data from tables. You can also alter the select statement with JOIN or UNION to retrieve data from multiple tables. Sometimes complex statement could be re-used in many places. This could cause some duplication within development. If there is a change on these table schemas, you may end up changing all these places. This is where views are coming.
Lets give an example
CREATE TABLE Offices (
OfficeID int NOT NULL PRIMARY KEY,
Title nvarchar(50)
);
Lets now create table with foreign key to use OfficeID column as reference value
CREATE TABLE Employees (
EmployeeID int NOT NULL,
Name nvarchar(50),
OfficeID int,
CONSTRAINT FK_Employees_Offices FOREIGN KEY(OfficeID) REFERENCES Offices(OfficeID),
PRIMARY KEY (EmployeeID)
);
Lets see these tables with data
Users Table
OfficeID Title
1 Office1
2 Office2
3 Office3
Employees Table
EmployeeID Name OfficeID
1 Employee1 1
2 Employee2 1
3 Employee3 2
4 Employee4 3
To retrieve Employee and it's related Office, the following select statement can be used
SELECT [EmployeeID] [EmployeeID]
,[Name] [EmployeeName]
,O.[OfficeID] [OfficeID]
,O.Title [OfficeTitle]
FROM [Employees] E
INNER JOIN Offices O ON E.[OfficeID] = O.[OfficeID]
Here is the result from the above statement
EmployeeID EmployeeName OfficeID OfficeTitle
1 Employee1 1 Office1
2 Employee2 2 Office2
3 Employee3 2 Office2
4 Employee4 3 Office3
Lets now create a view with the statement we previously used
CREATE VIEW VW_Employees AS
SELECT [EmployeeID] [EmployeeID]
,[Name] [EmployeeName]
,O.[OfficeID] [OfficeID]
,O.Title [OfficeTitle]
FROM [Employees] E
INNER JOIN Offices O ON E.[OfficeID] = O.[OfficeID]
Once you executed that statement, we can use the following statement to retrieve data with our new view
SELECT * FROM VW_Employees
Views can be removed with the following statement
DROP VIEW VW_Employees;