Friday, November 7, 2008

Nested JOINS (mix of inner and outer joins in SQL)

I just ran into this today:
Be Careful When Mixing INNER and OUTER Joins


It's not rocket science, but it can be somewhat counter-intuitive. How to tell the SQL server query engine what you want as an inner join vs an outer join.

I ended up using the nested join syntax by the way, seems cleanest to me and is not relying on the position of the JOIN line in the query.

Basically:




select People.PersonName, Pets.PetName, PetTypes.PetType
from People
left outer join
(Pets inner join PetTypes on Pets.PetTypeID = PetTypes.PetTypeID)
on Pets.OwnerID = People.PersonID

No comments: