- 5 years ago
- Zaid Bin Khalid
- 3,285 Views
-
3
In this section, we will learn about how to use inner join data from two different tables by using SQL inner join operation.
Inner Join
The most common type of join is an INNER JOIN. It returns only those rows which are a match in both joined tables.
Venn Diagram of Inner Join
The mention below, following the Venn diagram, shows how inner join works.
Proposed Tables
To understand this, let’s look at the following employees and departments tables given below.
Table: employees
+--------+--------------+------------+---------+
| 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 |
+--------+--------------+------------+---------+
Table: departments
+---------+------------------+
| dept_id | dept_name |
+---------+------------------+
| 1 | Administration |
| 2 | Customer Service |
| 3 | Finance |
| 4 | Human Resources |
| 5 | Sales |
+---------+------------------+
How to use Inner Join
So we begin now, let’s suppose you want to retrieve the data that includes id, name, hire date, and the department name of only those employees that have assigned to specific departments. Similar to our proposed tables, in real-time situations, you might face such conditions where you need to highlight the data of those employees who were not assigned to any other department. Hence to fulfill the requirements, we will learn how we can retrieve such particular data from both tables by using the same SQL queries. So let’s find out how we can do this.
By taking a closer look at the employees’ table, you might notice the column that stated as dept_id. This column contains the ID of the department assigned to each employee. Hence, the dep_id column of the employees’ table will work as a foreign key to the department table. So, this foreign key will work as a bridge between two tables.
Inner Join Example.
Have a look at the example that will retrieve employees’ id, name, hiring date, and department by using simple inner joining operation between two tables i.e., employees and department based upon standard column (dept_id). This inner join query will execute all the data of those employees who haven’t assigned to any specific 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;
Note: if you want to save time, it’s efficient to use table aliases in the query instead of typing long table names. For instance, you could give an alias name t1 to your employees’ table, and while referring to the emp_name column, you can only use t1.emp_name instead of employees.emp_name.
Output Results of Inner Join Operation.
After the execution of the above command, you will get the result set as mention below.
+--------+--------------+------------+-----------------+
| 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 |
+--------+--------------+------------+-----------------+
Now you can take a more in-depth look at the resulting set, which contains the data of only those employees whose dept_id value is present and also the value exists in the column dept_id() of the departments’ table.
- 5 years ago
- Zaid Bin Khalid
- 3,285 Views
-
3