eisenbahnfan eisenbahnfan - 28 days ago 6
SQL Question

How is "create view" evaluated in SQL?

I have a - maybe simple - question about how

create view
and
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
go

-- create the view
create view TEMP.VLT as
select LNR,TNR,MENGE
from LTP.LTP
go

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


A
view
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!
eisenbahnfan

Answer

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.

Comments