Are SQL views references, or a stored syntax to be evaluated at runtime?-CodePudding

As the question implies I'm not sure where SQL views are evaluated once during the session and then store the references, or are they store syntax which is evaluated at runtime? While it seems that it's second as same view can yield different results if the table which this view queries from is modified, It is confusing as for example when using alias for a table inside the view, this alias is not recognized when referring to it afterwards in the same statement which uses the view.

Thank you in advance.

CodePudding user response：

A stored view (CREATE VIEW AS) gets parsed at creation time, but is not much more than the mere code. When you run a query that uses this view, the whole query gets parsed and the code executed. (The exception to this is a materialized view as mentioned by LukStorms and The Impaler in the request comments.)

This query:

select from mytable t where id <= 100;

can be used as a view such:

select * from (select from mytable t where id <= 100) myview;

or such:

with myview as (select from mytable t where id <= 100)
select * from myview;

or such:

create view myview as select from mytable t where id <= 100;

select * from myview;

In neither option are you able to access the table alias t in the main query. It is hidden inside the subquery. The main query only sees the view name and its result.