正确使用join语句

正确使用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';