Creating View From Related Child Tables

I have the following general table structure (forgive the United States-centric carmakers in my contrived example): CREATE TABLE Car ( [Id] int PRIMARY KEY ) CREATE TABLE Ford

Solution 1:

First of all, let's try to see pros and cons of each of 2 approaches:

createview vw_Car1
asSELECT 
      c.Id,
      casewhen f.FordId isnotnullthen'Ford'else'Chevy'endas Maker,
      coalesce(f.Model, ch.Model) as Model
  FROM Car as c
  LEFTJOIN Ford as f on c.Id = f.FordId
  LEFTJOIN Chevy as ch on c.Id = ch.ChevyId
  WHERE (f.FordId isnotnullor ch.ChevyId isnotnull);

createview vw_Car2
asselect FordId as id, 'Ford'as Maker, Model from Ford
  unionallselect ChevyId as id, 'Chevy'as Maker, Model from Chevy;

The first one is better when you use it in joins, especially if you'll not using all of your columns. For example, let's say you have a view when you're using your vw_Car:

createtable people (name nvarchar(128), Carid int);

insertinto people
select'John', 1unionallselect'Paul', 2;

createview vw_people1
asselect
    p.Name, c.Maker, c.Model
from people as p
   leftouterjoin vw_Car1 as c on c.ID = p.CarID;

createview vw_people2
asselect
    p.Name, c.Maker, c.Model
from people as p
   leftouterjoin vw_Car2 as c on c.ID = p.CarID;

Now, if you want to do simple select:

select Name from vw_people1;

select Name from vw_people2;

First one would be simple select from people (vw_Car1 will not be queried at all). Second one will be more complex - Ford and Chevy will be both queried. You could think that first approach is better, but let's try another query:

select *
from vw_people1
where Maker = 'Ford'and Model = 'Fiesta';

select *
from vw_people2
where Maker = 'Ford'and Model = 'Fiesta';

Here second one will be faster, especially if you have index on Model column.

=> sql fiddle demo - see query plans of these queries.