正确使用join语句
常见的SQL语句类型有这么几种,DDL(数据定义语言),TPL(事务处理语言),DCL(数据控制语言),DML(数据操作语言),DML就是我们常说的增删改查。由架构通过对象生成的数据库语句往往比较低效,而正确使用SQL可以减少数据库服务器的负载,增加服务器稳定性,减少服务器间通讯的流量等(带宽成本很大)。这里我们主要讲一下join语句。
join从句可以分为5种:内连接(inner),全外连接(full outer),左外连接(left outer),右外连接(right outer),交叉连接(cross)
内连接
举这样一个例子,假设有两张表A和B,一张表存放者你的家庭成员和你自己,另一张表存放你的工作同事和你自己,他们都有一个user_name字段存放名字和一个position字段存放在群体中的位置。
内连接Inner join基于连接谓词将两张表(如A和B)的列组合在一起,产生新的结果表,找出两个表的公共部分(交集)
select a.'user_name',a.'position',b.'position'
from user1 a
inner join user2 b
on a.'user_name' = b.'user_name'
使用user_name作为连接谓词,执行完之后会发现新生成的表只有你的user_name和你在两个群体中position,找出了公共部分
左外连接
如果我们想查询家人表中是否有同事,即从左表中找出右表中有的数据,就可以使用左外连接
select a.'user_name', a.'position', b.'position'
from user1 a
left join user2 b
on a.'user_name' = b.'user_name'
where b.'user_name' is null
执行之后,这里如果没有使用where筛选,我们会发现左表中的数据全部被保留,筛选出的家庭成员表的第二个position字段就会为空。如果我们想过滤掉这些没有筛选成功的数据,就可以使用where
右外连接
使用right join和left join正好相反,一样要如果想过滤的话,可以使用where
全外连接
select a.'user_name', a.'position', b.'position'
from user1 a
full join user2 b
on a.'user_name' = b.'user_name'
我们发现报错了,我们查阅文档会发现SQL并不支持full join。那我们要怎么实现左连接和右连接的合集呢?
可以通过union all
select a.'user_name', a.'position', b.'position'
from user1 a
left join user2 b
on a.'user_name' = b.'user_name'
union all
//注意这里是b,右连接如果继续使用a有可能出现空值
select b.'user_name', a.'position', b.'position'
from user1 a
right join user2 b
on a.'user_name' = b.'user_name'
交叉连接
交叉连接(cross join),又成为笛卡尔连接。如果A和B是两个集合,它们的交叉连接就记为A x B
select a.'user_name', a.'position', b.'position'
from user1 a
cross join user2 b
这里没有连接谓词on
与join相关的SQL技巧
如何更新使用过滤条件中包括自身的表。如在这个例子中,把同时存在在表a和表b中的position更新为’top’
update user1
set position = 'top'
where user1.'user_name' in (
//结果集中
select a.'user_name',a.'position',b.'position'
from user1 a
inner join user2 b
on a.'user_name' = b.'user_name'
)
在mysql会发现报错了,那么我们可以通过这样的方式
update user1 a inner join user2 b on a.user_name = b.user_name
set a.position = 'top';