Views in SQL

Question

Recently I came across 'views' in SQL, and for me it seems really pointless,

Can somebody explain to me how views work or what's their purpose

Answer 1

Another way to look at this, view is a way to make data consumable for your application. You can think of it as an API. The business logic does not need to care about IDs in tables, and relationships between tables, as these are technical implementation details. The consumer cares about the actual values that make sense for business - sometimes even in a preprocessed, aggregated form. You could of course also compose your sql query in the application itself. But doing it on the database level, the DBA can also think about efficiency and performance of those queries, and make sure appropriate indexes exist on the tables.

Another similar concept is materialized views which actually replicates the data of the original tables. This can be useful, if the query is complicated and takes a long time to run, so you don't want to burden the transactional tables every time when the data is needed. You can refresh materialized views on demand, or automatically (like every hour or every day)

Answer 2

You can think of a 'view' as a saved query, persisted in the database, that does not duplicate the data in the original tables.
In relational databases, the data is typically normalized, so for example you have customers, orders, and order lines in different tables. When you want to answer a question like "who were my top customers in month X based on total sales amount" then you write a select statement, joining all those tables. But you may want to answer the same question over again, so saving this query as a view, is useful.
In addition, a view can show different information to different users, for example based on some environment variables. Like you can store the data of multiple companies in the same table, but based on the user who is querying the view, they should only see data of a single company.

Answer 3

A way to perform different action on without affecting the actual data in the database. E.g. say you deleted different rows from the view it wouldn’t effect the main table