Difference between views and SELECT queries

Question

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?

Answer 1

As Quassnoi said, it's useful for granting permission to certain rows in a table.

For example, let's say a lecturer at a university needs access to information on their students. The lecturer shouldn't have access to the "students" table because they could look up or modify information for any student in the whole university. The database admin makes a view that only shows students from the lecturers classes, and gives the lecturer the appropriate permissions for the view. Now the lecturer has access to their own students' data but not the whole "students" table.

Answer 2

• Using a view saves you copying and pasting your queries and adds code reusability, so you can change a single view instead of 10 queries in the different places of your code.
• Different permissions can be granted on views and tables, so that you can show only a portion of data to a user
• A view can be materialized, which means caching the results of the underlying query

Answer 3

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.