数据库的四种连接

SQL的四种连接

左外连接、右外连接、内连接、全连接

联接条件可在FROM或WHERE子句中指定,建议在FROM子句中指定联接条件。WHERE和HAVING子句也可以包含搜索条件,以进一步筛选联接条件所选的行。

1  内联接

(典型的联接运算,使用像 = 或 <> 之类的比较运算符)。包括相等联接和自然联接。
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如,检索 students和courses表中学生标识号相同的所有行。

2  外联接

外联接可以是左向外联接、右向外联接或完整外部联接
在 FROM子句中指定外联接时,可以由下列几组关键字中的一组指定:

  • 1)左外连接
    LEFT JOINLEFT OUTER JOIN
    左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值。

  • 2)右外连接
    RIGHT JOINRIGHT OUTER JOIN
    右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值

  • 3)全连接
    FULL JOINFULL OUTER JOIN
    完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。

3  交叉联接

交叉联接返回左表中的所有行,左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。

FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定;但是,用左或右向外联接指定表或视图时,表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息,请参见使用外联接。

辅助记忆:

连接方式结果
内连接不留未匹配行
左连接保留左侧未匹配行
右连接保留右侧未匹配行
全连接保留左和右侧未匹配行

示例:

有student,dormitory两个表

	# studnet 信息
	+----+------+-------+-------+
	| id | sid  | sname | did   |
	+----+------+-------+-------+
	|  1 | 1001 | Tom   | 40001 |
	|  2 | 1002 | Jack  | 40002 |
	|  3 | 1003 | Cindy | 41003 |
	|  4 | 1004 | Jerry |  NULL |
	+----+------+-------+-------+
	# dormitory 信息
	+----+-------+---------+
	| id | did   | dname   |
	+----+-------+---------+
	|  1 | 40001 | 4-4-001 |
	|  4 | 40002 | 4-4-002 |
	|  5 | 41003 | 5-1-001 |
	|  6 | 41004 | 5-2-003 |
	+----+-------+---------+

(1) 内连接

	# 显示住宿信息
	SELECT stu.sid,stu.sname,dorm.did FROM student stu,dormitory dorm WHERE stu.did=dorm.did;
	
	+------+-------+-------+
	| sid  | sname | did   |
	+------+-------+-------+
	| 1001 | Tom   | 40001 |
	| 1002 | Jack  | 40002 |
	| 1003 | Cindy | 41003 |
	+------+-------+-------+
	# 但是发现一个问题,Jerry同学的住宿信息并没有显示出来

(2) 左连接

	# 使用左连接显示暂时没有分配宿舍学生的信息
	# 左连接就是保留左侧孤立的值
	SELECT stu.sid,stu.sname,dorm.did FROM student stu LEFT JOIN dormitory dorm ON stu.did=dorm.did;
	+------+-------+-------+
	| sid  | sname | did   |
	+------+-------+-------+
	| 1001 | Tom   | 40001 |
	| 1002 | Jack  | 40002 |
	| 1003 | Cindy | 41003 |
	| 1004 | Jerry |  NULL |
	+------+-------+-------+

(3) 右连接

	# (1)中同样没有显示尚未有同学入住的宿舍的信息
	# 是有右连接保留右侧的孤立值
	SELECT stu.sid,stu.sname,dorm.did FROM student stu RIGHT JOIN dormitory dorm ON stu.did=dorm.did;
	+------+-------+-------+
	| sid  | sname | did   |
	+------+-------+-------+
	| 1001 | Tom   | 40001 |
	| 1002 | Jack  | 40002 |
	| 1003 | Cindy | 41003 |
	| NULL | NULL  | 41004 |
	+------+-------+-------+

(4) 完全链接

	# 在(3)有部分学生的信息没有展示 所以可以使用全连接查询所有住宿信息
	# 保留左右两侧孤立的信息
	SELECT stu.sid,stu.sname,dorm.did FROM student stu FULL JOIN dormitory dorm ON stu.did=dorm.did;
	+------+-------+-------+
	| sid  | sname | did   |
	+------+-------+-------+
	| 1001 | Tom   | 40001 |
	| 1002 | Jack  | 40002 |
	| 1003 | Cindy | 41003 |
	| 1004 | Jerry |  NULL |
	| NULL | NULL  | 41004 |
	+------+-------+-------+

注:MySQL不支持FULL JOIN
可以通过 左外连接 + UNION + 右外连接 实现

	 SELECT stu.sid,stu.sname,dorm.did FROM student stu LEFT JOIN dormitory dorm ON stu.did=dorm.did
	 union 
	 SELECT stu.sid,stu.sname,dorm.did FROM student stu RIGHT JOIN dormitory dorm ON stu.did=dorm.did

参考链接

此文只为讲解四种链接,所以数据表的设计存在依赖,并不是很好的数据表设计。


SQL

student表

	CREATE TABLE student (
	  id int(11) NOT NULL AUTO_INCREMENT,
	  sid int(11) NOT NULL,
	  sname varchar(20) NOT NULL,
	  did int(11) DEFAULT NULL,
	  PRIMARY KEY (`id`)
	);

	INSERT INTO student(`id`,`sid`,`sname`,`did`) VALUES (1,1001,'Tom',40001);
	INSERT INTO student(`id`,`sid`,`sname`,`did`) VALUES (2,1002,'Jack',40002);
	INSERT INTO student(`id`,`sid`,`sname`,`did`) VALUES (3,1003,'Cindy',41003);
	INSERT INTO student(`id`,`sid`,`sname`,`did`) VALUES (4,1004,'Jerry',NULL);

dormitory表

	CREATE TABLE dormitory (
	  id int(11) NOT NULL AUTO_INCREMENT,
	  did int(11) NOT NULL,
	  dname varchar(50) DEFAULT NULL,
	  PRIMARY KEY (`id`)
	);
	INSERT INTO dormitory(`id`,`did`,`dname`) VALUES (1,40001,'4-4-001');
	INSERT INTO dormitory(`id`,`did`,`dname`) VALUES (4,40002,'4-4-002');
	INSERT INTO dormitory(`id`,`did`,`dname`) VALUES (5,41003,'5-1-001');
	INSERT INTO dormitory(`id`,`did`,`dname`) VALUES (6,41004,'5-2-003');
  • 2
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值