I am working with MS SQL Server and the AdventureWorks2012 database in this lesson. If you want to play along, but do not have either installed on your computer, check out the following lessons to get you up and running:
Views
A view is a virtual table created in SQL that pulls information from existing tables in the database. To understand the need for a view, imagine you are the DBA for AdventureWorks constantly being asked to produce email addresses for employees. You are being asked this because whoever designed the front end of the database that your users interact with, left this vital piece of information out.
Finding an employee’s email address is simple enough. The SQL query below is all you need.
SELECT p.[FirstName] ,p.[MiddleName] ,p.[LastName] ,e.EmailAddress FROM [Person].[Person] as p join [Person].[EmailAddress] as e on p.BusinessEntityID = e.BusinessEntityID
Just add a where statement depending on if you are searching by first or last name.
But what if you are getting tired of having to create this query over and over every day. Well, you could make this query a permanent part of your database by turning it into a view.
The syntax for creating a view is simple. Just add Create View [NAME] and AS
create view HumanResources.vEmail as SELECT p.[FirstName] ,p.[MiddleName] ,p.[LastName] ,e.EmailAddress FROM [Person].[Person] as p join [Person].[EmailAddress] as e on p.BusinessEntityID = e.BusinessEntityID go
After you run this query, you can now find your view in the Object Explorer
You can now query the view just like you would any other table.
select * from HumanResources.vEmail where LastName like 'Ken%'
Views are great especially when dealing with very complex joins. Once you have figured out the proper query, you can just save it as a join and you won’t have to reinvent the wheel next time you need it.