The INNER JOIN is the most frequently used type of join. It only returns rows that have a matching entry in both joined tables. The Venn diagram below shows how an inner join functions.
To understand this better, let's look at the 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 fetch the id, name, hire date, and the department name of only those employees who are assigned to a specific department. In a real-life scenario, some employees might not be assigned to any department, like the fifth employee "Martin Blank" in our employees table. The question is, how do you retrieve data from both tables in one SQL query? Let's find out.
If you look at the employees table, you'll notice a column named dept_id, which contains the ID of the department each employee is assigned to. In technical terms, the dept_id column in the employees table is a foreign key to the departments table. We will use this column to link 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 using the dept_id column. It excludes 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;
Tip: When joining tables, it's a good practice to prefix each column name with its table name (e.g., employees.dept_id, departments.dept_id, or t1.dept_id, t2.dept_id if you're using table aliases). This helps avoid confusion and prevents ambiguous column errors if different tables have columns with the same name.
Note: To save time, instead of typing out the full table names, you can use table aliases in your query. For instance, you can assign the employees table an alias t1 and reference its column emp_name using t1.emp_name instead of employees.emp_name.
After running the above command, you will get a result set similar to 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 shown, the result set includes only those employees whose dept_id values are present and match the dept_id values in the departments table.