When you’re working with a database, there may be times where 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 also a list of the names of suppliers who have an ID associated with those products.
This is where
SQL joins come in. By using the
join condition, you are able to combine information from multiple tables into one result set. Using
joins can be useful when you need to get data across multiple tables but only want to run one query, instead of multiple queries.
In this tutorial, we are going to break down the basics of
SQL, how to use an
INNER JOIN, and where you may use the
INNER JOIN operator.
SQL Query Refresher
Retrieving information from an
SQL database can be done by executing a query. Queries almost always begin with the
SELECT statement and they allow you to get specific information from a database. Here is the usual structure of a query:
SELECT column_name FROM table_name WHERE our_conditions;
Let’s say that we wanted to get a list of the names and salaries of our employees. We could retrieve this information using the following query:
SELECT name, salary FROM employees;
Our query returned the following:
You can select as many column names as you want by separating them with a comma. Or, if you’re looking to get all the columns from a particular table, you can use an asterisk (
*) instead. However, without a
WHERE statement, the
SELECT keyword selects all rows.
Queries often include conditions after the
WHERE statement. These conditions filter out information so you can get exactly what you need from a database. For example, if you are looking for an employee’s branch name and email address, you could use the following query:
SELECT name, branch, email FROM employees WHERE name = 'Jonah';
Our query returns the following:
But what if we wanted to know what department Jonah works in? This is where a
JOIN query could be useful.
SQL Inner Join
INNER JOIN is the most common type of
SQL join. The operator returns records that have a match in both tables. For example, if you wanted to get the department name of every employee, which is stored in a separate table, you would use an
Right now, our database only stores the department ID of each employee in the
employees table. The department names are stored in a separate table called
|Geoff||Senior Sales Associate||1|
|Jonah||Vice President of Sales||3|
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, and the department names are in the
company_departments table. How do we get the information we are looking for?
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 to find out the name of the department for which that employee works.
Here is an example of a query that will retrieve 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:
|Geoff||Senior Sales Associate||Sales|
|Jonah||Vice President of Sales||Executive|
There is a lot going on in our query, so let’s break it down. On the first line, we tell the database to get the name and title of an employee from the
employees table, and the name of a company department from the
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
company_departments table together by their common fields, in this case the
join keyword selects information from two tables based on the query you have specified, then runs a match between the columns.
That’s all you need to know about the basics of
inner joins! In this tutorial, we have broken down how to use the
INNER JOIN operator to combine two tables in a query on an
As we have discussed,
INNER JOINs can be useful in a number of cases. If you needed to get supplier names for specific products, you could use an
INNER JOIN. Or if you needed to get the membership type names of customers who have certain membership IDs, you could use an
Mastering joins is an important part of querying
SQL databases, and this understanding will take you one step closer to becoming an