[MS SQL] Views & Joins

SQL Joins

To show differences between various joins modes, I need to check first which table, with common value (which is Forest District Number (named as ForestID in table ForestDistricts, SW_ID in SmallWild and LW_ID in LargeWild ), is longer. I created two separated selects as below:

Select Count(ForestID) as FD_Counter from ForestDistricts
Select Count(SW_ID) as SW_Counter from SmallWild

inner

select Fox, Marten, Hare from SmallWild
inner join ForestDistricts on (SW_ID=ForestID)

select ForestDistricts.ForestDistrict, SmallWild.Fox, 
SmallWild.Marten, SmallWild.Hare from SmallWild 
inner join ForestDistricts on (SW_ID=ForestID)

 select ForestDistricts.ForestDistrict, LargeWild.Deer, SmallWild.Fox
 from ((SmallWild
 inner join ForestDistricts on SW_ID=ForestID)
 inner join LargeWild on SW_ID=LW_ID);

result is column listed above for 144 records

left

 select Fox, Marten, Hare from SmallWild 
 left join ForestDistricts on (SW_ID=ForestID)

 select Fox, Marten, Hare from SmallWild 
 left outer join ForestDistricts on (SW_ID=ForestID)

result is column listed above for 144 records

right

select Fox, Marten, Hare from SmallWild 
right join ForestDistricts on (SW_ID=ForestID)
order by Fox

result is column listed above for 429 records with 285 NULLs

full

select ForestDistricts.ForestDistrict, SmallWild.Fox, 
SmallWild.Marten, SmallWild.Hare from SmallWild 
full outer join ForestDistricts on (SW_ID=ForestID)

union

SELECT ForestID, HuntingDistrict FROM ForestDistricts
WHERE ForestDistrict like 'G%'
UNION
SELECT ForestID, HuntingDistrict FROM ForestDistricts2
WHERE ForestDistrict like 'G%'
ORDER BY ForestID;

Views

Example 1

Create View Deers
as
select Deer, FallowDeer, Fawn from LargeWild
where Deer >= 2100
group by Deer, FallowDeer, Fawn 

Select * from Deers

Example 2

Create View WildAnimals
as
 select 
ForestDistricts.ForestDistrict,
LargeWild.Deer, 
SmallWild.Fox
 from ((SmallWild
 inner join ForestDistricts on SW_ID=ForestID)
 inner join LargeWild on SW_ID=LW_ID)
where ForestDistricts.ForestDistrict like 'A%'

Example 3

Create View WildAnimals2
as
 select
ForestDistricts.ForestDistrict,
LargeWild.Deer, 
SmallWild.Fox,
(LargeWild.Deer/SmallWild.Fox) as RandomINT
 from ((SmallWild
 inner join ForestDistricts on SW_ID=ForestID)
 inner join LargeWild on SW_ID=LW_ID)



drop View WildAnimals3;
drop View WildAnimals5;
drop View WildAnimals6;