How to use outer joins of data tables in MySQL

Why use outer connections

Before explaining why "outer connections" are used, let's take a look at a record. (As follows:)

As Zhang San in the table does not have a department number, we will temporarily classify him as a "temporary worker" without a fixed department establishment.

In such a scenario, problems arise. When we want to query the name of each employee and the department to which he belongs, in the case of using inner join, because our link condition is ` "Department Number" of "Employee Table" = "Department of "Department Table" Number", "Zhang San" will be missed. Although "Zhang San" does not have a "department number", he is also a member of the company as a "temporary worker", so the syntax of external joins must be introduced to solve this problem, otherwise some logical data will be lost.

Introduction to outer joins

The difference between outer joins and inner joins:

Only records that meet the connection conditions will appear in the result of the inner join, and records that do not meet the connection conditions will appear It will never appear in the result set.

Regardless of whether the data connection conditions are met, outer connections will be displayed in the result set in a special way. (For example, querying employee department number information mentioned above, because "Zhang San" does not have a department number, if an inner join is used, "Zhang San" does not meet the "connection conditions" and will not appear in the result set. ; Change to "outer join" and you will not miss it.)

Examples of outer joins are as follows

SELECT 
	e.empno, e.ename, d.dname
FROM
	t_emp e
LEFT JOIN t_dept d ON e.deptno = d.deptno;

-- 在连接的时候仍然是链接 "员工表" 与 "部门表" ，只不过连接关键字由 "JOIN" 变成了 "LEFT JOIN" (下文再为大家详细解释)
-- 两张表的连接条件还是使用 "ON" 关键字去连接的 ， 连接条件依然是 "员工表" 的 "部门编号" = "部门表" 的 "部门编号" 

-- LEFT JOIN 为 "外连接" 的 "左外连接" ；（在 "外连接" 中，是分为 "左外连接" 与 "右外连接" 的）

-- 在该SQL语句中 "LEFT JOIN" 左右各有数据表  "t_emp e" 与 "t_dept d" 
-- 所以这里的 "左连接" 的意思就是：保留 左表 的所有记录，然后与 右表 去连接，如果 右表 有符合条件的记录，则正常连接即可；
-- 如果 右表 没有符合条件的连接记录， 右表 则展示 "NULL" 值与 "左表" 去匹配

Left joins and right joins

"Left outer join" means that during the connection operation, all records in the left table are retained and connected to the right table. The left table will be connected to the right table. If there are records that meet the conditions in the right table; if there are no records that meet the conditions in the right table, "NULL" will be used to connect the left table.

The difference from "left join" is "right join". "Right join" is the opposite of "left join". It retains all the records in the right table and joins the qualified records in the left table; the same , if the left table does not have records that meet the conditions, use "NULL" to join the right table.

Right join SQL statement example:

SELECT 
	e.empno, e.ename, d.dname
FROM
	t_dept d
RIGHT JOIN t_emp e ON e.deptno = d.deptno;

-- 这里有个需要注意的地方，就是相较于上文中的 "左连接" ，这里的 "右连接" 左右两张的表的位置做了调换

Here, you can see that you can still find out that "Zhang San" does not have a "department number" record of. So the difference between "left join" and "right join" is not very big.

Outer join exercise ①

Query the name of each department and the number of people in the department?

This question seems simple, but there are two difficulties in it, and there are also areas where mistakes are easy to make. For details, see the SQL statement examples and schematic diagram below.

SELECT 
    d.deptno, d.dname, COUNT(*)
FROM
    t_dept d LEFT JOIN t_emp e 
ON d.deptno = e.deptno
GROUP BY d.deptno;

OK, this is where the problem starts.

Everyone pay attention to the "40" - "OPERATIONS" department here. There is actually no one in this department, that is, the number of people is "0", but strangely, when statistics are performed here, there is The number of people counted is "1". Why is this?

This is because when we use grouping, we use "left join" and retain all the data in the left table, so we follow the left table's "deptno" for grouping. (Because the records of the left table are retained, the grouping also needs to be grouped according to the left table. The next key is "COUNT(*)", which will count the number of all valid records. So when all the records of the left table "t_dept" When the record is connected to the right table "t_emp", the right table will use the "NULL" value to connect to the left table "t_dept". After the connection is completed, it will be a valid record. Since it is a valid record, then "COUNT(*)" The statistical result is "1".

So, it is understandable that the statistical result of 40 departments is "1", but this result is not what we want. How to go about it How to solve it? Refer to the SQL statement below.

SELECT 
    d.deptno, d.dname, COUNT(e.deptno)
FROM
    t_dept d LEFT JOIN t_emp e 
ON d.deptno = e.deptno
GROUP BY d.deptno;

This SQL statement is still very good. There are many details and unconsidered situations. Only if you really write it once Only when these attacks will be noticed.

Outer join exercise ②

Retrieve the department name and number of people. For employees who do not have a department, use "NULL" instead of the department name. (This actually refers to "Zhang San")

Maybe you will think that what you just used is a "left outer join" to retain all the records in the department table. Isn't it just a "right" outer join? In fact... . It’s not that simple.

The SQL statement of this exercise needs to be implemented using the "UNION" keyword. Use the "UNION" keyword to merge the result sets of multiple query statements (to exclude duplicates) content).

"UNION"关键字 在 SQL 语句中的用法如下：

(SQL查询语句) UNION (SQL查询语句)         -- 如果存在多条查询语句的话，可以继续使用 UNION 关键字 连接

PS：这里需要注意一下，“UNION” 合并多少个结果集其实无所谓，关键是这些结果集的字段数量和字段的名称必须要相同 。如果说第一个 SQL 查询语句返回的是 10个 字段，第二个返回的是 2个字段 ，这种情况是完全没办法合并的。

(SELECT 
	d.deptno, d.dname, COUNT(e.deptno)
FROM
	t_dept d LEFT JOIN t_emp e 
ON d.deptno = e.deptno
GROUP BY d.deptno)
UNION
(SELECT 
	d.deptno, d.dname, COUNT(*)
FROM
	t_dept d RIGHT JOIN t_emp e 
ON d.deptno = e.deptno
GROUP BY d.deptno);

-- 第一个查询语句，得到的结果集是各个部门的人数。
-- 第二个查询语句，得到的结果集是隶属于各个部门的人数，但是因为 "张三" 是一个没有部门所属的 "临时工"
-- 所以两个查询语句的结果集合并之下没救如下图所示。

The above is the detailed content of How to use outer joins of data tables in MySQL. For more information, please follow other related articles on the PHP Chinese website!