从相关子表创建视图

从相关子表创建视图

首先，让我们尝试看看两种方法各自的优缺点：

create view vw_Car1as  SELECt       c.Id,      case when f.FordId is not null then 'Ford' else 'Chevy' end as Maker,      coalesce(f.Model, ch.Model) as Model  FROM Car as c  LEFT JOIN Ford as f on c.Id = f.FordId  LEFT JOIN Chevy as ch on c.Id = ch.ChevyId  WHERe (f.FordId is not null or ch.ChevyId is not null);create view vw_Car2as  select FordId as id, 'Ford' as Maker, Model from Ford  union all  select ChevyId as id, 'Chevy' as Maker, Model from Chevy;

当在联接中使用它时，第一个更好，特别是如果您不使用所有列。例如，假设您在使用时拥有一个视图

vw_Car

：

create table people (name nvarchar(128), Carid int);insert into peopleselect 'John', 1 union allselect 'Paul', 2;create view vw_people1asselect    p.Name, c.Maker, c.Modelfrom people as p   left outer join vw_Car1 as c on c.ID = p.CarID;create view vw_people2asselect    p.Name, c.Maker, c.Modelfrom people as p   left outer join vw_Car2 as c on c.ID = p.CarID;

现在，如果要进行简单选择，请执行以下 *** 作：

select Name from vw_people1;select Name from vw_people2;

第一个是简单的选择

people

（

vw_Car1

根本不会查询）。第二个将更为复杂-

Ford

而且

Chevy

将会对其进行查询。您可能认为第一种方法更好，但是让我们尝试另一个查询：

select *from vw_people1where Maker = 'Ford' and Model = 'Fiesta';select *from vw_people2where Maker = 'Ford' and Model = 'Fiesta';

在这里第二个会更快，尤其是当您在

Model

列上有索引时。

= > sql fiddle演示-请参阅这些查询的查询计划。