Difference between views and SELECT queries
sql, view
Solution
All answers above provide an excellent explanation for the difference between a view and a query.
The query in the question is simple to an extreme degree, and creating a view for it might be overkill. However, most queries are more complex, for example:
;with Orders2016 as (
select Customers.CustomerID
, Customers.CompanyName
, TotalOrderAmount = sum(OD.Quantity * OD.UnitPrice)
from Customers
join Orders O on Customers.CustomerID = O.CustomerID
join OrderDetails OD on O.OrderID = OD.OrderID
where OrderDate >= '2016-01-01'
and OrderDate < '2017-01-01'
group by Customers.CustomerID, Customers.CompanyName
)
, CustomersGroups as
(
select CustomerID
, CompanyName
, TotalOrderAmount
, CustomerGroup =
(
case
when
TotalOrderAmount >= 0 and TotalOrderAmount < 1000
then 'low'
when TotalOrderAmount >= 1000 and TotalOrderAmount < 5000
then 'Medium'
when TotalOrderAmount >= 5000 and TotalOrderAmount < 10000
then 'High'
when TotalOrderAmount >= 10000 then 'VeryHigh'
end
)
from Orders2016
)
select CustomerGroup
, TotalInGroup = Count(*)
, PercentageInGroup = Count(*) * 1.0 / (select count(*) from CustomersGroups)
from CustomersGroups
group by CustomerGroup
order by TotalInGroup desc;
Imagine rewriting it each time you want to access data. (Or even searching through files, copy & paste). Poor time management.
Views also save us tons of time and are a way to be DRY.
Problem
If views are used to show selected columns to the user, and same can be done by using ``` SELECT col1, col2 FROM xyz ``` , what is the point of using views?