Basic SQL Tutorial

SQL Join (Join)

Advanced SQL Tutorial

SQL Reference Manual

SQL INNER JOIN Statement

In this tutorial, you will learn how to use SQL INNER JOIN to retrieve data from two tables.

Using INNER JOIN

INNER JOIN is the most commonJoin Type. It only returns the rows that match in both joined tables. The inner join operation is explained by the Venn diagram below.

To make this easier to understand, let's take a look at the followingemployeesanddepartments表。

+--------+--------------+------------+---------+
| 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, suppose you only need to retrieve the emp_id, emp_name, hire_date, and dept_name of employees assigned to a specific department. Because in reality, some employees may not have been assigned to a department, for example, weemployeesThe fifth employee in the table is 'Martin Blank'. However, the question here is how to retrieve data from two tables in the same SQL query?

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

This is an example that retrieves the employee's id, name, hire date, and department by connecting the employee and departments tables using the common dept_id column. It does not include employees who have not been assigned to any department.

Example

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, please add the name of the table it belongs to before each column name (for example, employees.dept_id, departments.dept_id, or t1.dept_id, t2.dept_id If you usetable aliasto avoid any confusion and ambiguity in column errors with the same name in different tables.

Note:To save time, you can usetable aliasto replace typing the long table name. For example, you can assign an alias t1, and use t1Instead of using .emp_name instead of employees.emp_name to refer to its column emp_name. After executing the above command, you will get the following result set:

+--------+--------------+------------+-----------------+
|　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 only contains employees with a dept_id value that also exists in the dept_id column of the department table.

SQL LEFT JOIN SQL Join Query (Join)