内连接

概念

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

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

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

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

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

inner join/join

在 SQL 中，还可以使用 INNER JOIN 或 JOIN 关键字来执行内连接。两个或多个表格可以通过 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 JOIN、RIGHT 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 匹配的 id 为 4、5、6 的三行也会显示到最终的查询结果中，右表部分会以 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 匹配的 id 为 3 的一行也会显示到最终的查询结果中，左表部分会以 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)