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.
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.
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!
You must be logged in to post a comment.