【MySQL】区分:等值连接/自连接/自然连接/外连接 以及ON和Where使用

一、等值连接

等值连接:它是基于两个表之间的相等测试,展现出来的是共同的数据,因此又称内部连接。

select * from a , b where  a.id = b.id;
select * from a inner join b on a.id = b.id;
select * from a inner join b using (`id`); 

除了inner join,还有left join和right join。

二、自连接

自连接:给一张表取两个不同的别名就,使用两个别名将表与表本身进行连接查询,然后附上连接条件。

select * from a , b where  a.id = b.id;

三、自然连接

自然连接:自然连接是一种特殊的等值连接。它要求两个关系表中进行比较的分量必须是相同的属性组,并且在结果中把重复的属性列去掉。而等值连接并不去掉重复的属性列。

# 自然连接

-- 使用NATURAL JOIN 
SELECT 
	*
FROM 
	`a` NATURAL JOIN `b`

四、外连接

外连接包括左外连接、右外连接、全外连接。

1.左外连接

左外连接:两表进行连接时,将返回左表的所有行。如果左表的某行在右表中没有匹配行,则将为右表返回空值。

# 左外连接
-- 使用LEFT JOIN (left out join)
SELECT 
	*
FROM 
	`a` LEFT JOIN `b`
-- USING(`id`);
ON
	`a`.`id` = `b`.`id`;
2.右外连接

右外连接:两表进行连接时,将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。

# 左外连接
-- 使用LEFT JOIN (left out join)
SELECT 
	*
FROM 
	`a` RIGHT JOIN `b`
-- USING(`id`);
ON
	`a`.`id` = `b`.`id`;
3.全外连接

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

# 全外连接

-- 使用FULL JOIN,mysql 不支持,orcal支持
SELECT 
	*
FROM 
	`a` FULL JOIN `b`
-- USING(`id`);
ON
	`a`.`id` = `b`.`id`;
	
-- mysql中使用UNION来实现FULL JOIN的功能
SELECT 
	*
FROM 
	`a` LEFT JOIN `b`
-- USING(`id`);
ON
	`a`.`id` = `b`.`id`
UNION
SELECT 
	*
FROM 
	`a` RIGHT JOIN `b`
-- USING(`id`);
ON
	`a`.`id` = `b`.`id`;

五、using 和 on

using 和 on 值可用于 left join 、right join 、full join ,不可用于 natural join ,因为 natural join 不需要显示的连接条件。

  • using:当连接条件中的两张表的两个连接属性相同时可以使用using。

  • on:连接条件中的两张表的两个连接属性不论是否相同,都可用 on, using 是 on 两个连接属性相同时的简写

六、JOIN 关联表中 ON、WHERE 后面跟条件的区别

数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户。

join联结可以简单理解为以下过程:

  1. 首先两个表做一个笛卡尔积。
  2. 然后根据on后面的条件对这个笛卡尔积做一个过滤形成一张临时表。
  3. 如果有where就对上一步的临时表再进行过滤,进而得到最终的结果集。
  4. 也就是说如果inner join left join联查过滤条件放在on中就是在第二步进行的过滤;如果过滤条件放在where中就是在第三步进行的过滤。

inner join和cross join的区别:

  • cross join是笛卡尔积,理论上不应该支持on语法,inner join是内连接,支持on
  • SQLServer中cross join 后面加on 是错误的。mysql中使用inner join和cross join加不加on都不会报错。
  • 在 MySQL 中(仅限于 MySQL) CROSS JOIN 与 INNER JOIN 的表现是一样的,在不指定 ON 条件得到的结果都是笛卡尔积,反之取得两个表完全匹配的结果。INNER JOIN 与 CROSS JOIN 可以省略 INNER 或 CROSS 关键字

在使用left jion时,on和where条件的区别如下:

1、 on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。

2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。

最后几点总结:

  • 当使用left join时,无论on的条件是否满足,都会返回左表的所有记录,对于满足的条件的记录,两个表对应的记录会连接起来,对于不满足条件的记录,那右表字段全部是null
  • on后加了and就是 关联满足条件表2的数据,但是结果数据不做过滤,显示全部表1数据
  • on是在生成连接表的起作用的where是生成连接表之后对连接表再进行过滤,相当于where之前做了子查询,返回满足where条件的数据。

比如:

select * 
form tab1 
left join tab2 
	on (tab1.size = tab2.size and tab2.name=’AAA’);
这个查询返回结果还是会显示name为AAA的记录,因为on只作关联tab2的条件,并不会对tab1进行过滤作用。
select * 
form tab1 
left join tab2 
	on (tab1.size = tab2.size) 
where tab2.name=’AAA’

这个结果就不显示name为AAA的记录。因为生成临时表后,where对行记录再次过滤。
  • 4
    点赞
  • 18
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 6
    评论
评论 6
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Itfuture03

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值