文章目录
- MySQL
- MySQL表的内连接和外连接
- 1. 内连接
- 2. 外连接
- 2.1 左外连接
- 2.2 右外连接
- 3. 内外连接的区别
- 4. 左外连接和右外连接的区别
MySQL
MySQL表的内连接和外连接
MySQL 中的内连接(INNER JOIN)和外连接(包括左外连接 LEFT JOIN 和右外连接 RIGHT JOIN)是 SQL 查询中用于从两个或多个表中获取数据的重要工具。这些连接操作基于两个表之间的某些相关列之间的关系。下面,我将详细介绍这些连接的概念和用法。
1. 内连接
内连接(INNER JOIN):内连接返回两个表中满足连接条件的所有行。如果某行在其中一个表中没有匹配的行,那么该行就不会出现在结果集中。内连接是最常见的连接类型,通常用于获取两个表中具有相关数据的行。
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
简单示例:我们创建一张表用于员工信息的存储:
其中包括员工的编号 id ,员工的姓名和工作,员工的薪资和入职日期,以及员工的部门编号和所对应的领导编号。其中编号 empno 作为我们的主键。
mysql> create table emp(
-> empno int unsigned primary key,
-> ename varchar(20),
-> job varchar(20),
-> salary int,
-> hiredate date,
-> deptno int,
-> );
忘了添加或者后续想要添加关于员工其他的列信息?我们可以使用 alter 进行对应表的修改以此来添加我们所需要的列信息。
mysql> alter table emp
-> add column mgr int;
查看表的结构:
desc emp;
插入信息:
mysql> insert into emp values(1001,'张三','总经理',10000,'2000-1-1',10,NULL);
mysql> insert into emp values(1002,'张花','员工',5000,'2005-7-4',10,1001);
mysql> insert into emp values(1003,'李四','经理',8000,'2002-3-12',20,NULL);
mysql> insert into emp values(1004,'李华','销售',8000,'2006-3-12',10,1001);
mysql> insert into emp values(1005,'王五','销售',7800,'2007-11-28',20,1002);
mysql> insert into emp values(1006,'赵六','销售',6700,'2008-5-18',10,1001);
mysql> insert into emp values(1007,'小美','员工',5000,'2010-2-08',20,1003);
mysql> insert into emp values(1008,'小帅','经理',9000,'2002-3-12',30,NULL);
mysql> insert into emp values(1009,'小蓝','员工',7600,'2007-5-22',30,NULL);
查看表的所有信息:
mysql> select *from emp;
显示张花的名字和部门名称:
在之前我们通常使用笛卡尔积进行两张表的连接,使用 select+where 查询 就是属于内连接的一种方法。
select ename,dname
from emp,dept
where emp.deptno=dept.deptno and ename='张花';
使用这个内连接就是相当于换另一种写法,结果还是和 使用 select+where 查询后的结果 效果是一样的。
select ename,dname
from emp
inner join dept
on emp.deptno=dept.deptno and ename='张花';
2. 外连接
外连接:外连接返回左表(对于左外连接)或右表(对于右外连接)中的所有行,以及右表(对于左外连接)或左表(对于右外连接)中满足连接条件的行。(如果某行在其中一个表中没有匹配的行,那么结果集中该行的对应列将包含 NULL 值)。
2.1 左外连接
左外连接(LEFT JOIN 或 LEFT OUTER JOIN):左外连接返回左表中的所有行,以及右表中满足连接条件的行(如果左表中的某行在右表中没有匹配的行,那么结果集中该行的右表列将包含 NULL 值)。
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
使用左外连接查询所有的信息:
select *
from emp
left join dept
on emp.deptno=dept.deptno;
2.2 右外连接
右外连接(RIGHT JOIN 或 RIGHT OUTER JOIN):右外连接返回右表中的所有行,以及左表中满足连接条件的行(如果右表中的某行在左表中没有匹配的行,那么结果集中该行的左表列将包含 NULL 值)。
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
使用右外连接查询所有的信息:
select *
from emp
right join dept
on emp.deptno=dept.deptno;
结合内连接查询来看:
三种查询的结果是一致的,就是在顺序上有一些区别,内连接的查询结果和右连接查询结果是完全一样的因为它们都是以empno编号先为顺序进行连接,而左外连接是先以deptno的编号先为顺序进行连接。我们根据不同的需要选择我们合适的连接方式。
注意一下:外连接的连接方式不可以省略,内连接可以。
当使用LEFT JOIN(或任何JOIN类型)时,你必须指定连接条件。你的查询没有提供ON子句来指定emp表和dept表如何关联。因此,MySQL不知道如何在两个表之间建立连接。查询应该包含一个ON子句,指明连接条件。
3. 内外连接的区别
内连接和外连接之间的主要区别:
(1)返回的行数:
内连接:只返回满足连接条件的行。如果某行在其中一个表中没有匹配的行,那么该行不会出现在结果集中。
外连接:返回至少一个表中的所有行,以及另一个表中满足连接条件的行。对于没有匹配的行,结果集中对应的列将包含 NULL 值。
(2)结果集内容
内连接:结果集仅包含两个表中共有的数据,即两个表中都有匹配的行。
外连接:结果集不仅包含共有的数据,还包含至少一个表中独有的数据(以 NULL 值表示)。
(3)类型
内连接:只有一种类型,即标准的 INNER JOIN。
外连接:有三种类型:LEFT JOIN(或 LEFT OUTER JOIN)、RIGHT JOIN(或 RIGHT OUTER JOIN)和 FULL JOIN(或 FULL OUTER JOIN)。LEFT JOIN 返回左表中的所有行和右表中匹配的行;RIGHT JOIN 返回右表中的所有行和左表中匹配的行;FULL JOIN 返回两个表中的所有行。
(4)用途
内连接:通常用于查询两个或多个表中具有相关数据的行,例如查询订单和订单详情。
外连接:用于查询一个表中的所有记录,以及与之相关的另一个表中的记录,即使某些记录在另一个表中没有匹配项。这在需要显示完整列表,包括没有相关数据的项时非常有用,例如在报告或数据分析中。
4. 左外连接和右外连接的区别
左外连接和右外连接的主要区别:
左外连接:返回左表中的所有记录和右表中匹配的记录。 如果右表中没有与左表匹配的记录,则结果集中对应的右表字段将为NULL。这种连接类型特别适用于你想要获取左表中的所有记录,并查看哪些右表的记录与之匹配的场景。
而右外连接:返回右表中的所有记录和左表中匹配的记录。 如果左表中没有与右表匹配的记录,则结果集中对应的左表字段将为NULL。这种连接类型则适用于你想要获取右表中的所有记录,并查看哪些左表的记录与之匹配的情况。