DEV Community

Wendy Calderon
Wendy Calderon

Posted on • Updated on

SQL: Inner Join

In SQL we have various powerful clauses that make managing databases possible, but one that usually brings confusion along with it's functionality, is the JOIN clause.

The JOIN clause is used in a relational database to select records from two or more tables that share at least one column in common. There are different ways of joining tables, and in this post we will be discussing, through the use of an example, the one corresponding to the INNER JOIN.

INNER JOIN will compare the specified tables and select those rows that have matching values. It could be considered the default JOIN type, since we would get the same result without specifying the keyword INNER.

It's syntax is the following:

Inner Join syntax

In the SELECT clause, we specify the columns we want to select, by chaining them to the table they correspond. Pro-tip: we only do this for columns that are not specific to one table. If we want to select a column that is specific to just one table, we don't have to specify the table name.

Now let's explain how it works, based on an example. The following image shows the tables to be considered.
Alt Text
In this example, we see that in the pets table we have a pet_id column that is common to the checkups table. The pet_id column in the pets table is referred to as Primary Key, and the one in the checkups table as Foreign Key.

The next image graphically explains the result of joining these two tables with the INNER clause, referencing the Venn Diagram.

Alt Text

Now, considering the tables above, if we wanted to get our pets names, their species and the date they had a checkup, the query would look like this:

Example query

Let's break it down: We select the columns we want, and since they are unique columns to each of the tables, we don't have to chain them to their tables. We select them from pets, and join the checkups table on the column both tables have in common (remember, the INNER keyword is optional). We could've selected from checkups and joined pets, as long as we join them on their common field, we would be getting the same result.

In a following post, we'll discuss the other ways available to join tables. See you there!

E.T.A.: The following post, where we discuss Outer Joins, can be found here.

Discussion (4)

Collapse
katnel20 profile image
Katie Nelson

What if both tables had a column called 'name'. How would you select the name of the checkups table?

Collapse
wendisha profile image
Wendy Calderon Author

Good question Katie.
When a column name is not unique to a table, you would chain it to it's table name, so it would be SELECT checkups.name FROM ...

Collapse
katnel20 profile image
Katie Nelson

Makes perfect sense. Thanks Wendy.

Thread Thread
wendisha profile image
Wendy Calderon Author

Glad to help! :)