eisenbahnfan eisenbahnfan - 1 year ago 51
SQL Question

How is "create view" evaluated in SQL?

I have a - maybe simple - question about how

create view
select ... from viewname
is evaluated. I know how to create views and use them, but I have to explain the procedure of those two commands graphically and with text for my school.

Well, I do the following (I am using Microsoft SQL Server 2012 at the moment, but that should not matter, does it? I would like to know it for MySQL too, if there are differences):

-- create a Schema for the test
create schema TEMP

-- create the view
create view TEMP.VLT as
from LTP.LTP

-- select the data from the view
select * from TEMP.VLT

is just something like an alias for a select query, which is evaluated in something like a subquery, right? If so, am I right, that

select * from TEMP.VLT

is the same as

select * from (select LNR,TNR,MENGE from LTP.LTP) as VLT


I mean, I know that it gives the same result, but is it actually evaluated the same way, or am I wrong?

Thank you for responsing!

Answer Source

Your understanding is correct at a very high level. First, views can differ among databases. MySQL, in particular, has a bunch of limitations such as no subqueries in the FROM clause. But, in fact, MySQL pretty much operates the way you describe.

You have the right understanding in general. The code of the view is merged into the query where it is being referenced. Here is specific language in the documentation:

When an SQL statement references a nonindexed view, the parser and query optimizer analyze the source of both the SQL statement and the view and then resolve them into a single execution plan. There is not one plan for the SQL statement and a separate plan for the view.

There is a type of view called "indexed views" which actually materialize the data. In this case, the code for the view is not actually run, but results are used in the query.