An INNER JOIN is used to get rows that match in each table. In this instance we want to know which cats also live with a dog—each pet must have the other. If they don’t, they won’t show up in our results. When they have to exist in both tables, we use an INNER JOIN.
To help us understand this relationship, it may be helpful to visualize the relationship. In this case we are going to join the Dog table and the Cat table. When an owner has both a cat and a dog, the pets will appear in our query.
The syntax for a join query is to specify the join type and then the table to be joined. We then use the keyword ON with the relationship columns. Our INNER JOIN example will look like this:
SELECT [NameOfCat], [NameOfDog] FROM Dog INNER JOIN Cat ON Dog.OwnerIdentifier = Cat.OwnerIdentifier;
DogIdentifier NameOfDog BreedOfDog NameOfCat
———— ———– ————— ————-
500 Odie Beagle Garfield
743 Aragorn German Shorthaired Pointer Arwen
579 Spike Border Collie Bella
586 Jake Border Collie Bella
747 Murry Jack Russell Terrier Bella
749 Rover Labrador Retriever Lucy
751 Pongo Lancashire Heeler Luna
753 Rex Maltese Kitty
582 Spike Border Collie Piper
589 Lila Braque du Bourbonnais Piper
755 Rover Large Munsterlander Piper
Here we can see the cat Bella has 3 dogs in her home. Because she matches on more than one record in the dog table, she is listed for matched record in the dog table.
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!