SQL 基础教程

SQL 连接(Join)

SQL 高级教程

SQL 参考手册

LEFT JOIN SQL Consultas de Tabelas Relacionadas SQL (Join)

SQL INNER JOIN 语句

在本教程中，您将学习如何使用SQL内部联接从两个表中获取数据。

使用内部联接

INNER JOIN是最常见的连接类型。它仅返回在两个联接表中都匹配的行。下维恩图说明了内部联接的工作方式。

为了容易理解这一点，让我们来看看下面employees和departments表。

+--------+--------------+------------+---------+
|　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'. But 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, that is, 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 to which each column belongs before each column name (for example, employees.dept_id, departments.dept_id, or t1.dept_id, t2.dept_id If you usetable alias）to avoid any confusion and ambiguity due to column name conflicts 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 t1Use .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　|　Recursos Humanos　|
|　　　　　　2　|　Tony　Montana　|　2002-07-15　|　Administração　　|
|　　　　　　3　|　Sarah　Connor　|　2005-10-18　|　Sales　　|
|　　　　　　4　|　Rick　Deckard　|　2007-01-03　|　Finance　　|
+--------+--------------+------------+-----------------+

Como você vê, o conjunto de resultados contém apenas funcionários com dept_id e este valor também existe na coluna dept_id da tabela department.

LEFT JOIN SQL Consultas de Tabelas Relacionadas SQL (Join)