What are the implementation methods of MySQL inner joins, outer joins and SQL JOINS?

1. Inner join

Inner join: merge rows from more than two tables with the same column. The result set does not contain one table and another table does not Matching lines.

To put it bluntly, the query results only include the rows they match, and the unmatched rows are discarded.

[Example] Query employee number employee_id and its corresponding department name department_name. Among them, the department name department_name is only in the department table departments, and the department table departments is as shown in the following figure:

Employee tableemployees and department tabledepartments are connected by matching department number department_id. The query code is as follows:

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp, departments dept
WHERE emp.`department_id` = dept.`department_id`;

Query results:

106 records are returned here, but the total number of employees table employees is There are 107 records, and one person is missing. The reason is that in the employee table employees, there is an employee whose department number department_id is (NULL), as shown in the following figure:

There is no department number department_id with a value of (NULL) in the department table departments, so the unmatched data in this row is discarded and not displayed. As shown in the figure below, the inner join only contains matching rows of the two tables, that is, the intersection of the two circles in the figure below:

This join method is called inner join.

2. Outer join

Outer join: Merge the rows of more than two tables with the same column. In addition to the rows matching one table and another table, the result set also queries Reached a non-matching row in the left or right table.

Outer joins are divided into the following three categories:

Left outer join:

In addition to returning rows that meet the join conditions during the join process, the two tables also return left Rows in the table that do not meet the conditions. As shown in the picture below, the left outer join is the entire circle on the left.

Right outer join:

During the connection process, in addition to returning rows that meet the connection conditions, the two tables also return right Rows in the table that do not meet the condition. As shown in the picture below, the right outer join is the entire circle on the right.

Full outer join:

During the table connection process, in addition to returning rows that meet the join conditions, the left table and the right table will also be returned Rows that do not meet the criteria. As shown in the figure below, the full outer join is all the parts of the two circles.

[Example] Based on the department number department_id, query all employee numbers in the employee table employees employee_id and its corresponding department name department_name in the department table departments.

[Analysis] Whenever there are words in the question that require querying all, we must be very alert. This means that we need to use outer joins to query. Both SQL92 and SQL99 syntax can be used to implement outer joins. For details, see [5.9 Commonly Used SQL Standards] (# 5.9 Commonly Used SQL Standards). Since the left table employee table employees has a total of 107 pieces of data, and the right table and the left table match only 106 pieces of data, a left outer join needs to be used.

[SQL92 syntax to implement outer joins] Use ( ).

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp, departments dept
WHERE emp.`department_id` = dept.`department_id`(+);

Query result: Error

This is because MySQL does not support the outer join operation of SQL92 syntax. But Oracle supports it. So there is no learning in vain. MySQL only supports SQL99 syntax to implement multi-table queries.

3. SQL99 syntax to implement multi-table query

SQL99 refers to the SQL syntax standard specification promulgated by SQL in 1999. Although a series of new SQL standards were released later, in the process of learning MySQL, it is enough to mainly master SQL99 and SQL92. Starting from this section, the study of MySQL is halfway through, because before this section was SQL92 syntax, starting from this section is exclusively for SQL99 syntax.

SQL99语法使用 JOIN...ON 的方式实现多表查询，且可以同时实现内连接和三种外连接。MySQL是支持这种方式的。

3.1 SQL99实现内连接

【例子：三表查询】查询员工的员工编号 employee_id 、 姓名 last_name 、部门名称 department_name 和所在城市 city 。

【分析】这个需求需要 3 张表共同查询。

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`, loc.`city`
FROM employees emp JOIN departments dept
ON emp.`department_id` = dept.`department_id`
JOIN locations loc
ON dept.`location_id` = loc.`location_id`;

SQL99语法就是加一张表，就 JOIN 一张表，并在 ON 后加连接条件。注意，这里的 JOIN 前面还省略了表示内连接的关键字 INNER ，在使用内连接时可以忽略。即代码还可以写成完整形式：

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`, loc.`city`
FROM employees emp INNER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
JOIN locations loc
ON dept.`location_id` = loc.`location_id`;

查询结果：

3.2 SQL99语法实现外连接

3.2.1 左外连接

【例子】根据部门编号 department_id ，查询员工表 employees 中的所有员工编号 employee_id 和部门表 departments 中其对应的部门名称 department_name 。

【分析】由于左表是员工表 employees ，有107条数据；而右表是部门表 departments ，有27条数据。题目要求是返回所有员工的107条查询结果，因此这里使用左外连接。SQL99实现左连接接很简单，只需要在 JOIN 前加上两个关键字 LEFT OUTER 即可表示左外连接。如下代码所示：

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

其中，OUTER 可以省略，即写成：

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

查询结果：

3.2.2 右外连接

举一反三地，右外连接就是在 OUTER JOIN 前加一个关键字 RIGHT 。

SELECT emp.`employeed/master/img/d`;

查询结果：

查询结果有122条记录，这怎么解释呢？再回想一下右外连接的定义：

两个表在连接过程中除了返回满足连接条件的行以外，还返回右表中不满足条件的行。如下图中，右外连接就是右边一整个圆。

就不难理解，因为右表部是没有人的。而左、右表匹配的数据有106条 (两圆相交部分) ，因此一共就有 106 + 16 = 122 106+16=122 106+16=122 条记录。如下图所示：

这个例子能更好地帮助我们理解右外连接。

3.2.3 满外连接

举一反三地，满外连接就是在 OUTER JOIN 前加一个关键字 FULL 。但很不幸，MySQL不支持SQL99的满外连接语法，Oracle是支持的。

我们需要使用别的方法实现MySQL中的满外连接，详见4.6 满外连接 。

4.总结：七种SQL JOINS的实现

在开始本节之前，需要您了解SQL的 UNION 和 UNION ALL 的定义和实现。如果需要了解，可以阅读这篇博文：《MySQL中 UNION 并的使用》。

4.1 内连接

根据部门编号 department_id ，查询员工表 employees 中的员工编号 employee_id 和部门表 departments 中其对应的部门名称 department_name 。

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

查询结果：

4.2 左外连接

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

查询结果：

4.3 右外连接

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

4.4 第四种JOIN

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE dept.`department_id` IS NULL;

查询结果：

作用是把员工表 employees 中，部门编号 department_id 为 (NULL) 的那一个员工查询出来了，如下图所示：

4.5 第五种JOIN

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`, emp.`department_id`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE emp.`department_id` IS NULL;

查询结果：

4.6 满外连接

由于MySQL不支持SQL99语法的满外连接。因此，我们的实现方式就是求

4.2 左外连接 和 4.5 第五种JOIN 的并 UNION ALL 即可；或者求4.3 右外连接 和 4.4 第四种JOIN 的并 UNION ALL 也行，都是一样的效果。

方法一

方法二

# 方法一
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
UNION ALL
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_id`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE emp.`department_id` IS NULL;

# 方法二
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`employee_id` = dept.`department_id`
UNION ALL
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`employee_id` = dept.`department_id`
WHERE dept.`department_id` IS NULL;

查询结果：

4.7 第七种JOIN

实现下面这个操作只需要把 4.4 第四种JOIN 和 4.5 第五种JOIN 求 UNION ALL 即可。

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE dept.`department_id` IS NULL
UNION ALL
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE emp.`department_id` IS NULL;

查询结果：

The above is the detailed content of What are the implementation methods of MySQL inner joins, outer joins and SQL JOINS?. For more information, please follow other related articles on the PHP Chinese website!