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 | +---------+------------------+ |
|
Table: employees |
Table: departments |
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.
Example
Try this code »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;Tip: When joining tables, prefix each column name with the name of the table it belongs to (e.g. employees.dept_id, departments.dept_id, or t1.dept_id, t2.dept_id if you're using the table aliases) in order to avoid confusion and ambiguous column error in case columns in different tables have the same name.
Note: To save time, in place of typing the long table names you can use table aliases in the query. For example, you can give the employees table an alias name t1 and refer its column emp_name using t1.emp_name instead of employees.emp_name
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.
