文章目录
- 内连接
- 概念
- inner join/join
- 外连接
- 左外连接
- 右外连接
- 全外连接
内连接
概念
内连接(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 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 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 employeeleft join departmenton 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 employeeright join departmenton 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 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)