(SQL Tutorials for Citizen Data Scientist)

SQL INNER JOIN Operation

In this tutorial you will learn how to fetch data from two tables using SQL inner join.

Using Inner Joins

The INNER JOIN is the most common type of join. It returns only those rows that have a match in both joined tables. The following Venn diagram illustrates how inner join works.

To understand this easily, let’s look at the following employees and departments tables.

+--------+--------------+------------+---------+
| emp_id | emp_name     | hire_date  | dept_id |
+--------+--------------+------------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |       4 |
|      2 | Tony Montana | 2002-07-15 |       1 |
|      3 | Sarah Connor | 2005-10-18 |       5 |
|      4 | Rick Deckard | 2007-01-03 |       3 |
|      5 | Martin Blank | 2008-06-24 |    NULL |
+--------+--------------+------------+---------+

+---------+------------------+
| dept_id | dept_name        |
+---------+------------------+
|       1 | Administration   |
|       2 | Customer Service |
|       3 | Finance          |
|       4 | Human Resources  |
|       5 | Sales            |
+---------+------------------+

Now, let’s say you need to retrieve the id, name, hire date, and the department name of only those employees who assigned to a particular department. Because, in real-life scenario there may be some employees who are not yet assigned to a department, like the fifth employee “Martin Blank” in our employees table. But the question here is, how to retrieve the data from both the tables in the same SQL query? Well, let’s find out.

If you see the employees table, you’ll notice that it has a column named dept_id which holds the ID of the department to which each employee is assigned i.e. in technical terms, the employees table’s dept_id column is the foreign key to the departments table, and therefore we will use this column as a bridge between these two tables.

Here’s an example that retrieves the employee’s id, name, hiring date and their department by joining the employees and departments tables together using the common dept_id column. It excludes those employees who are not assigned to any department.

SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 INNER JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY emp_id;

After executing the above command, you get the result set something like this:

+--------+--------------+------------+-----------------+
| emp_id | emp_name     | hire_date  | dept_name       |
+--------+--------------+------------+-----------------+
|      1 | Ethan Hunt   | 2001-05-01 | Human Resources |
|      2 | Tony Montana | 2002-07-15 | Administration  |
|      3 | Sarah Connor | 2005-10-18 | Sales           |
|      4 | Rick Deckard | 2007-01-03 | Finance         |
+--------+--------------+------------+-----------------+

As you can see, the result set contains only those employees for which the dept_id value is present and that value also exists in the dept_id column of the departments table.

Beginners Guide to SQL – SQL INNER JOIN Operation

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.