Exercise 20: Inner and Outer Joins

You have been learning how to join (link) tables using simple equality (person.id = person_pet.person_id), and sub-select queries. There is one more way to link them with INNER JOIN and OUTER JOIN. I personally don't use these two types of joins because I always have to look up what they do and why they exist. They seem like some odd feature that was added to SQL because one of the companies selling databases added it to their server and that meant everyone else had to have it too. The phrases "inner" and "outer" also make no sense in the context of tables. They are just arbitrary words that you have to memorize to use them, rather than a description of what they actually do. I'm sure someone smarter than me will be able to explain the reason these words were chosen but who cares. They are weird and not very useful to be brutally honest.

This exercise is mostly so you'll know what these are when you see them, and you'll know to avoid these kinds of joins without first doing some serious research. Let's start this discussion with three example queries on our database:

select * from pet, person, person_pet
    where person.id = person_pet.person_id 
    and pet.id = person_pet.pet_id 
    and person.first_name = 'Zed';

select * from pet join person, person_pet 
    on person.id = person_pet.person_id 
    and pet.id = person_pet.pet_id 
    and person.first_name = 'Zed';

select * from pet left outer join person, person_pet 
    on person.id = person_pet.person_id 
    and pet.id = person_pet.pet_id 
    and person.first_name = 'Zed';

Run these queries and analyze the output in the WYSS section where I'll discuss what's going on.