Does an SQL view duplicate data-CodePudding

Under the hood, does a view create a duplicate/copy of data?

CREATE VIEW Deliveries
AS
SELECT o.OrderNo, o.OrderDate,
       c.FirstName, c.LastName, c.Address, c.City
FROM Order AS o JOIN Customer AS c
ON o.Customer = c.ID;

I can query the view and filter the data in much the same way as a table using the following query:

SELECT OrderNo, OrderDate, LastName, Address
FROM Deliveries
WHERE City = 'Seattle';

Assuming that a view speeds up access to data, does a view does it by duplicating data? If so, does an sql database ensure data integrity by updating the view if the original table is updated?

CodePudding user response：

view is like just a shadow of the table , phycically it will not present in the database, and yes if table is updated then the related view will also update. (you have to run that view query when you feel data is modified)

CodePudding user response：

The answer to your question depends on whether or not the view is materialized. In a non materialized view, the view is basically just a wrapper for its underlying select query. A non materialized view itself does not contain or duplicate any data.

On the other hand, a materialized view behaves more like a table than a view as described above. In this case, all data in the view exists somewhere in its own place on the database. In addition, if the data in the table(s) underlying the materialized view changes, then the view must be updated.