文章目录

  • 内连接
    • 概念
    • 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 table2ON table1.column_name = table2.column_name;

显示SMITH的名字和部门名称

-- from where 的写法select ename, dnamefrom emp, deptwhere emp.deptno = dept.deptno and ename = 'SMITH';-- 标准的内连接写法select ename, dnamefrom empinner join depton emp.deptno = dept.deptnowhere 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 employeeleft join departmenton 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 employeeright join departmenton 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 employeeleft join departmenton employee.department_id = department.idunionselect *from employeeright join departmenton 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)