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]
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!