> 文章列表 > 【MySQL】(8)内外连接

【MySQL】(8)内外连接

【MySQL】(8)内外连接

文章目录

  • 内连接
    • 概念
    • inner join/join
  • 外连接
    • 左外连接
    • 右外连接
    • 全外连接

内连接

概念

内连接(Inner Join)是 SQL 查询中最常用的连接方式之一,它用于在两个或多个表之间根据共同的字段将数据进行合并。

内连接基于两个或多个表之间的共同列(也称为关联列)进行匹配,然后返回匹配行的组合。匹配的条件ON 子句指定。如果两个表中的行不匹配,则不会包含在内连接的结果集中。

上一节,我们在 from 后面跟多张表,然后使用 where 来筛选其实就是内连接

内连接之所以被称为内连接,是因为它只返回满足连接条件的行,而不包含不满足连接条件的行。也就是说,内连接只关心两个表格之间的共同部分,而忽略了不匹配的行。

内连接的另一个常见名称是等值连接(Equi-Join),因为内连接通常基于两个表格之间的等值关系进行匹配,即两个表格的关联列上的值相等。内连接还可以基于不等的关系进行匹配,如使用 <><=>=<> 等操作符,这样的连接称为不等值连接(Non-Equi-Join)。

inner join/join

在 SQL 中,还可以使用 INNER JOINJOIN 关键字来执行内连接。两个或多个表格可以通过 JOIN 关键字进行连接,使用 ON 子句指定连接条件

语法

SELECT column1, column2, ...
FROM table1
[INNER ]JOIN table2
ON table1.column_name = table2.column_name;

显示SMITH的名字和部门名称

-- from where 的写法
select ename, dname
from emp, dept
where emp.deptno = dept.deptno and ename = 'SMITH';-- 标准的内连接写法
select ename, dname
from emp
inner join dept
on emp.deptno = dept.deptno
where ename = 'SMITH';

Q:使用 INNER JOIN 和使用 FROM WHERE 两种方式,哪个好呢?

A:使用 INNER JOIN 更加简洁明了,语法结构清晰,可以在 ON 子句中指定连接条件,易于阅读和维护。同时,使用 INNER JOIN 还可以避免笛卡尔积和重复数据等问题,提高查询效率和性能。

而使用 FROM 子句和 WHERE 子句的方式进行内连接,语法相对复杂,需要指定多个表格之间的连接条件,易于出现语法错误和逻辑错误,而且不能清晰地区分连接条件和筛选条件。在语句复杂度较高时,使用 INNER JOIN 可以更好地维护和优化 SQL 语句。

综上所述,INNER JOIN 更加简洁明了,易于阅读和维护,而且效率更高,建议使用 INNER JOIN 进行内连接。

外连接

与内连接不同,外连接可以包含左表、右表或两个表中所有的行,即使没有匹配的行也会包含在结果集中。外连接基于两个或多个表之间的共同列(也称为关联列)进行匹配,匹配的条件由 ON 子句指定。

在 MySQL 中,可以使用 LEFT JOINRIGHT JOIN 关键字来执行外连接。

左外连接

将两表称为左表和右表,左外连接相当于对两表先做内连接,然后将左表中未出现在内连接结果中的行补上去,其右表部分以 NULL 填充。

LEFT JOIN 用于进行左外连接

以下表为例

CREATE TABLE department (id INT PRIMARY KEY,name VARCHAR(50)
);CREATE TABLE employee (id INT PRIMARY KEY,name VARCHAR(50),department_id INT,salary INT
);INSERT INTO department (id, name) VALUES (1, 'Sales');
INSERT INTO department (id, name) VALUES (2, 'Marketing');
INSERT INTO department (id, name) VALUES (3, 'Finance');INSERT INTO employee (id, name, department_id, salary) VALUES (1, 'John', 1, 50000);
INSERT INTO employee (id, name, department_id, salary) VALUES (2, 'Jane', 1, 55000);
INSERT INTO employee (id, name, department_id, salary) VALUES (3, 'Bob', 2, 60000);
INSERT INTO employee (id, name, department_id, salary) VALUES (4, 'Alice', 4, 70000);
INSERT INTO employee (id, name, department_id, salary) VALUES (5, 'Mark', NULL, 80000);
INSERT INTO employee (id, name, department_id, salary) VALUES (6, 'Emily', NULL, 90000);

select *
from employee
left join department
on employee.department_id = department.id;

上述查询以 employee 为左表,department 为右表,employee 中无法与 department 匹配的 id456 的三行也会显示到最终的查询结果中,右表部分会以 NULL 显示

查询结果:

+----+-------+---------------+--------+------+-----------+
| id | name  | department_id | salary | id   | name      |
+----+-------+---------------+--------+------+-----------+
|  1 | John  |             1 |  50000 |    1 | Sales     |
|  2 | Jane  |             1 |  55000 |    1 | Sales     |
|  3 | Bob   |             2 |  60000 |    2 | Marketing |
|  4 | Alice |             4 |  70000 | NULL | NULL      |
|  5 | Mark  |          NULL |  80000 | NULL | NULL      |
|  6 | Emily |          NULL |  90000 | NULL | NULL      |
+----+-------+---------------+--------+------+-----------+
6 rows in set (0.00 sec)

右外连接

将两表称为左表和右表,右外连接相当于对两表先做内连接,然后将右表中未出现在内连接结果中的行补上去,其左表部分以 NULL 填充。

RIGHT JOIN 用于进行右外连接

select *
from employee
right join department
on employee.department_id = department.id;

上述查询以 employee 为左表,department 为右表,department 中无法与 employee 匹配的 id3 的一行也会显示到最终的查询结果中,左表部分会以 NULL 显示

+------+------+---------------+--------+----+-----------+
| id   | name | department_id | salary | id | name      |
+------+------+---------------+--------+----+-----------+
|    1 | John |             1 |  50000 |  1 | Sales     |
|    2 | Jane |             1 |  55000 |  1 | Sales     |
|    3 | Bob  |             2 |  60000 |  2 | Marketing |
| NULL | NULL |          NULL |   NULL |  3 | Finance   |
+------+------+---------------+--------+----+-----------+
4 rows in set (0.00 sec)

全外连接

在 MySQL 中,没有专门的语法来实现全连接(full outer join)。但是可以通过使用左外连接和右外连接的组合来模拟实现全连接。

使用 UNION 操作符将左外连接和右外连接的结果合并在一起,从而实现全连接的效果。

select *
from employee
left join department
on employee.department_id = department.id
union
select *
from employee
right join department
on employee.department_id = department.id;

查询结果:

+------+-------+---------------+--------+------+-----------+
| id   | name  | department_id | salary | id   | name      |
+------+-------+---------------+--------+------+-----------+
|    1 | John  |             1 |  50000 |    1 | Sales     |
|    2 | Jane  |             1 |  55000 |    1 | Sales     |
|    3 | Bob   |             2 |  60000 |    2 | Marketing |
|    4 | Alice |             4 |  70000 | NULL | NULL      |
|    5 | Mark  |          NULL |  80000 | NULL | NULL      |
|    6 | Emily |          NULL |  90000 | NULL | NULL      |
| NULL | NULL  |          NULL |   NULL |    3 | Finance   |
+------+-------+---------------+--------+------+-----------+
7 rows in set (0.00 sec)

松山湖