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;

OUTPUT (Modified)

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.

I

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!