SQL INNER JOIN: A Complete Guide

The SQL INNER JOIN statement returns rows with exact values in two columns across two tables. You can join a table across one or multiple columns. JOIN statements connect two database tables and compare their contents.

There may be times when you want to get data from two or more tables at the same time and combine the result.

For example, you may want to get a list of products and the names of suppliers who have supplied those products. The information for product names and supplier names is in two tables.

This is where SQL joins come in. By using the join condition, you are able to combine information from multiple tables intoe following: one result set. The SQL INNER JOIN is a type of join that returns records who have matching column values in two tables. In this tutorial, we are going to discuss how to use an INNER JOIN.

SQL INNER JOIN

The SQL INNER JOIN retrieves all the records with column values that appear in two specified tables. An INNER JOIN statement uses the ON keyword to match the data across the two tables.

The INNER JOIN is the most common type of SQL join. The operator returns records that have a match in both tables.

Let’s take a look at the syntax of the INNER JOIN keyword:

SELECT name

FROM customers

INNER JOIN plans

ON customers.plan_id = plans.id;

This query combines rows from the “customers” and “plans” tables. It returns a list of all customers with a “plan_id” equal to any ID In the “plans” table.

We use the INNER JOIN clause to join our tables. The ON keyword comes after the name of the table we join. You can specify multiple conditions after the ON keyword by separating them with commas.

INNER JOIN SQL Example

Right now, we have an employee database that stores the department ID of each employee in the employees table. We created this table with an SQL CREATE TABLE statement. The department names are stored in a separate table called company_departments.

Here is the employees table:

(7 rows)

This is the departments table:

(3 rows)

Let’s say that we wanted to get the names, titles, and department names for all employees. This data is contained within two tables: employee names and titles are in the employees table. The department names are in the company_departments table.

How do we get the information we are looking for?

The employees table has a column named department_id. This column holds the ID of the department for which each employee works.

In the company_departments table, each department also has an ID (which is a primary key in the table). This means we can match an employee’s department_id with the list of departments. Doing so will let us find out the name of the department for which that employee works.

Let’s write a query that retrieves an employee’s name, their title, and the name of the department for which they work:

SELECT Employees.Name, Employees.Title, Company_Departments.Name
FROM Employees
INNER JOIN Company_Departments
ON Employees.Department_ID = Company_Departments.Department_ID;

Our query returns the following matching rows:

On the first line, we tell the database to get the name and title of an employee from the employees table. We also retrieve the name of a company department from the company_departments table.

On the second line, we specify that we want to get the employee information from the employees table. Next, we use our INNER JOIN to connect our employees table with the company_departments table, which includes the names of departments.

Finally, our last line links both the employees and company_departments table together by their common fields, in this case the department_id.

"Career Karma entered my life when I needed it most and quickly helped me match with a bootcamp. Two months after graduating, I found my dream job that aligned with my values and goals in life!"

Venus, Software Engineer at Rockbot

Find Your Bootcamp Match

Conclusion

The SQL INNER JOIN statement retrieves records with two matching column values across two tables. The ON statement lets you specify the conditions on which your inner join executes.

Mastering joins is an important part of querying SQL databases, and this understanding will take you one step closer to becoming an SQL expert.

We have written a how to learn SQL guide to help learners like you master SQL. This guide contains a list of top learning resources and courses as well as advice on how to learn SQL. Check out the guide on our How to Learn SQL page.