5 Easy SQL INNER JOIN Examples for Beginners

Looking for a clear explanation of joins in SQL? Check out these five SQL INNER JOIN examples!

In SQL, INNER JOINs can be a bit difficult for beginners to master. But once you start working with them, you’ll learn they’re very useful! Let’s discuss five examples of SQL INNER JOINs. But first, let’s do a quick review of why JOINs matter.

In relational databases, data is organized and stored within tables. Each table represents a specific type of information. But, oftentimes, you need to analyze data from different tables simultaneously. And this is where JOINs step in.

We encourage you to practice joining tables by following our interactive SQL JOINs course. It includes 93 exercises covering the following topics:

• SQL JOIN types.
• Multiple JOINs.
• Self-joins, i.e. joining a table with itself.
• Non-equi JOINs.

So, are you ready to look at some examples of INNER JOIN? Let’s get started!

Bringing Tables Together: Introducing SQL JOIN Operations

SQL JOINs combine data from two or more tables based on matching column values. For instance, you can merge customers’ information with the orders they made or link each product to its suppliers.

SQL JOINs enable you to join data from different tables and extract meaningful information for your specific use case. Check out this SQL JOIN Cheat Sheet to get the overview of different types of JOINs.

An INNER JOIN in SQL combines rows from multiple tables by matching their common column values. When you apply an INNER JOIN to link customer data with their corresponding orders, you'll see a list of customers who have placed at least one order.

In addition to INNER JOIN, SQL also provides other types of joins: LEFT JOIN, RIGHT JOIN, and FULL JOIN. Those joins are called OUTER JOINs. Unlike an INNER JOIN, an OUTER JOIN operation can list rows from both tables, even if there is no match.

Let’s look at an example to better understand the nature of SQL INNER JOINs.

Here is the customers table that stores customer information:

And here is the orders table that stores all orders made by the customers:

The common column (shown in pink) on which the customers and orders tables are joined is the customer_id column.

Now, here’s the query:

SELECT 
  c.customer_id, 
  c.email, 
  o.order_date, 
  o.order_amount
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id;

This query joins the customers table (aliased AS c) and the orders table (aliased AS o). These table aliases provide a clear way to inform the database of the source table of each column (using the syntax table_alias.column_name).

These aliases are employed within the ON clause to establish the column for joining tables. We also  use them in the SELECT statement, where two columns are selected from the customers table (c.customer_id and c.email) and another two columns from the orders table (o.order_date and o.order_amount).

Here is the output of the query:

You might observe that the customer with the ID of 3 does not appear in the resulting table. This particular customer hasn’t placed any orders yet; thus, they don’t have any matching records in the orders table.

The INNER JOIN keyword is interchangeable with the JOIN keyword. In other words, when you use JOIN, the database interprets it as a shorthand notation for INNER JOIN.

To learn more about INNER JOINs, check out our article What Is an Inner Join in SQL?.

SQL INNER JOIN Examples in Practice

Let's explore some SQL INNER JOIN examples tailored for beginners. Before jumping into the examples, check out this article on how to practice SQL JOINs.

Example 1: Link Books with Authors

You’ve got two tables that store information about books and authors. Join these tables to see the list of books along with their authors.

Here is the books table:

The book_id column uniquely identifies each book. And the author_id column assigns an author to each book.

And here is the authors table:

The author_id column uniquely identifies each author. And the author_name column stores authors’ full names.

If you want to see book titles and their author names, you can join these tables based on their common column (the author_id column in both tables). Just select title from books and author_name from authors:

SELECT 
  b.title, 
  a.author_name
FROM books AS b
INNER JOIN authors AS a
ON b.author_id = a.author_id;

This is the resulting table:

Please note that each book is assigned one author and each author is assigned one book, as there are no duplicate values in the common author_id column. Examine the next example to see what happens if the common column has duplicate values.

Example 2: Assign Products to Categories

You’ve got two tables that store products and product categories. Join these tables to see the list of products along with their categories.

Here is the products table:

The product_id column uniquely identifies each product. And the category_id column assigns a category to each product.

And here is the categories table:

The category_id column uniquely identifies each category. And the category_name column stores full category names.

If you want to see products and their categories, you can join these tables based on their common column, which is the category_id column. Here’s the query:

SELECT 
  p.product_name, 
  c.category_name
FROM products AS p
INNER JOIN categories AS c
ON p.category_id = c.category_id;

This is the resulting table:

The categories with IDs of 22 and 23 are assigned to two products each; their names appear twice in the output table.

Example 3: List Doctors and Patients with the Same First Name

You’ve got two tables that store information about doctors and patients. Join these tables to see the list of doctors and patients who share the same first name.

Here is the doctors table:

The doctor_id column uniquely identifies each doctor. The other two columns store doctors’ first and last names.

And here is the patients table:

The patient_id column uniquely identifies each patient. The other two columns store patients’ first and last names.

If you want to see the list of doctors and patients who share the same first name, you can join these tables based on the first_name column.

SELECT d.*, p.*
FROM doctors AS d
INNER JOIN patients AS p
ON d.first_name = p.first_name;

Please note that d.* defines all columns from the doctors table and p.* defines all columns from the patients table.

This is the resulting table:

Thanks to the INNER JOIN operation, you can list only the doctor and patient pairs where the first names are the same.

Example 4: Match People to Suitable Apartments

You’ve got two tables that store information about apartments available for rent and people looking for apartments that fit their price range. Join these tables to see which apartments can be rented by which person.

Here is the apartments table:

The apartment_id column uniquely identifies each apartment. And the rent column stores the monthly rent amount.

And here is the persons table:

The person_id column uniquely identifies each person who is looking for an apartment to rent. The email column stores their emails and the max_rent column stores the maximum amount of monthly rent they are able to pay for the apartment.

If you want to see which apartments can be rented by which person, you can join these tables based on the rent and max_rent columns:

SELECT 
  p.person_id, 
  p.email,
  a.apartment_id, 
  a.rent AS apartment_rent
FROM apartments AS a
INNER JOIN persons AS p
ON a.rent < p.max_rent;

Here the match must be made between the rent column of the apartments table and the max_rent column of the persons table, where rent < max_rent.

This is the resulting table:

By using the ON clause with the condition containing the < sign, multiple rows from the apartments table were assigned to multiple rows from the persons table, and vice versa. And you got to see which apartments are suitable to rent for which persons.

Example 5: Join Continents, Countries, and Cities Tables

This time you’ve got three tables that store information about continents, countries, and cities. Join these tables to learn which city belongs to which country and which country belongs to which continent.

Here is the continents table that stores all continents:

The continent_id column uniquely identifies each continent. And the continent_name column stores its full name.

Here is the countries table that stores selected countries:

The country_id column uniquely identifies each country. The country_name column stores its full name. And the continent_id column stores the continent where the country is located.

And here is the cities table that stores selected cities:

The city_id column uniquely identifies each city. The city_name column stores its full name. And the country_id column stores the country where the city is located.

If you want to see which cities, countries, and continents go together, you can join these three tables based on their common columns:

SELECT 
  cont.continent_name, 
  coun.country_name, 
  ci.city_name
FROM continents AS cont
INNER JOIN countries AS coun
ON cont.continent_id = coun.continent_id
INNER JOIN cities AS ci
ON coun.country_id = ci.country_id;

The continent table is joined to the countries table on their common column (continent_id). And the countries table is joined to the cities table on their common column (country_id).

This is the resulting table:

The three tables are joined on their common columns and the SELECT statement lists continent, country, and city names based on the join between ID columns.

Check out this article on joining three or more tables in SQL for more examples of multi-table joins.

Your Turn to Practice SQL INNER JOIN Examples

Throughout this article, we've delved into the intricacies of INNER JOIN in SQL, from the basic concepts to more advanced scenarios. We used various JOIN conditions and even joined three tables. As we've seen, the power of INNER JOIN lies in its ability to seamlessly connect datasets, allowing us to extract valuable insights.

Do you need more SQL INNER JOIN examples? Try your hand at all types of JOINs by following our course on SQL JOINs that we mentioned earlier. You’ll get all the hands-on practice you need to get comfortable writing JOINs.

Good luck!