We use a RIGHT OUTER JOIN when we want to get all the values from the table on the right, or the ‘joined’ table, and then see the matching data from the first table, or the table on the left. The now familiar join syntax will be RIGHT OUTER JOIN followed by ON with the join criteria. In order to help demonstrate this more clearly, I will order by the column CatIdentifier. I do this because I expect to see a record for every cat even if they don’t have an accompanying dog record.

[sql]SELECT NameOfCat, NameOfDog, BreedOfDog
FROM Dog
RIGHT OUTER JOIN Cat ON Dog.OwnerIdentifier = Cat.OwnerIdentifier
ORDER BY Cat.CatIdentifier;[/sql]

OUTPUT

NameOfCat      NameOfDog       BreedOfDog
———–             ————–            ————-

Garfield            Odie                    Abruzzenhund
Ellie                  NULL                   NULL
Dexter             NULL                   NULL
Arwen             Aragorn               Labany
Milo                 NULL                   NULL
Bella                Murry                  Labradoodle
Bella                Spike                   Border Collie
Bella               Jake                      Border Collie
Lucy                Rover                  Labrador Retriever
Luna              Pongo                  Lancashire Heeler

(317 row(s) affected)

Remember—NULL values mean there is no information for that column.

Our visualization for a RIGHT OUTER JOIN would look like this.

RightJoin

 

 

Now you may be wondering here—What if I switched the order of the tables, could I use a Left Outer Join? Yes, you could. If you noticed that, go eat a piece of candy as your reward. 🙂

Personal Preference: I rarely use RIGHT OUTER JOINs because it makes more sense to line up my columns ‘from the left’ and move right. For this reason, I use LEFT OUTER JOINs 99.99% of the time.

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!