Difference between views and SELECT queries Difference between views and SELECT queries sql sql

Difference between views and SELECT queries

  • 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

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.

A view can be described as a virtual table, created from a SQL query stored in the database.

Therefore, the following are aspects to consider in using VIEWS

  • Performance: it can improve data access performance as queries involving several tables generate transactions already exchanged to generate the view.

  • Simplicity: most of the views I work with are data arrangements of columns from 4+ tables, a bunch of inner joins. Once the view is created, your application developers will have to deal with the SELECT statements using column in the same view, hence the term virtual table.

  • Security: or just called it access control. Most relational database management system allow properties in the view object that control the type of access. For instance, one can allow users to update a view but only the DBA can make modifications to the tables that compose the view.