We use a LEFT OUTER JOIN when we want to see all the rows of one table and any rows that match our join criteria from the second. Each join syntax is similar, so you won’t be surprised to see the keyword LEFT OUTER JOIN is used to define the join along with the keyword ON and the join criteria. In this query all rows from the table listed first (the left) will be returned and only the matching rows from the second table (right table) will be included.
SELECT DogIdentifier, NameOfDog , BreedOfDog , NameOfCat FROM Dog LEFT OUTER JOIN Cat ON Dog.OwnerIdentifier = Cat.OwnerIdentifier;
DogIdentifier NameOfDog BreedOfDog NameOfCat
————- ————- ————– ————-
500 Odie Abruzzenhund Garfield
501 Laddie Afador NULL
502 Jack Bergamasco NULL
503 Ace Berger de Picard NULL
504 Ace Berger des Pyrenees Jake
504 Ace Berger des Pyrenees Bella
505 Jack Bernese Mountain Dog NULL
506 Jack Bernese Rottie NULL
507 Jack Bhagyari Kutta NULL
508 Pluto Bloodhound Maggie
(369 row(s) affected)
Here we are introduced to something called a NULL value—it is the database telling you it has no value for the data you query. In this case, there are several dogs who do not have cats in the home, so the NameOfCat column shows a NULL because there is no matching row.
Want to follow along on your own database?
Do you want to follow along with the examples? We have created a sample database you can use to follow along and try in your own database. Go to the download page and follow the instructions. Have fun on the SQL trail!