SQL JOIN 连接查询

在数据库的设计中，一个表以一个对象为主题，比如，一张以学生为主题，一张以班级为主题，另一张以教师为主题。我们在使用过程中需要将这些信息汇总按我们需要的新的主题维度，就要对表进行连接。当然，有时同一个表我们可以解析出不同的维度再进行连接。

基本语法

SELECT <list3>
FROM
  (SELECT <list1>
   FROM T) AS t1
<LEFT / RIGHT> JOIN
  (SELECT <list2>
   FROM C) AS c1 ON t1.id = c1.id AND t1.name = c1.name

ON 为两个表的连接点，建议基础表先 select 出来，使逻辑更加明确。

逻辑说明

注：连接点可以多个。

逻辑图示

案例

以下将学生表和班级表进行连接：

select s.name,
       s.class,
       c.teacher
from students as s
         join class c on s.class = c.class

更加推荐的写法：

select s.name    as name,   -- 姓名
       s.class   as class,  -- 班级
       c.teacher as teacher -- 老师
from (select name,
             class
      from students) as s
         left join
     (select class,
             teacher
      from class) as c on s.class = c.class
where s.class in (2,3)
'''
name    class   teacher
王琳  2   王老师
李成  2   王老师
赵天成 3   李老师
王卫栋 2   王老师
武明  3   李老师
'''

其中：

• hive 的 join 语法仅支持等值连接，不支持非等值的连接
• 将基础表和被连接表独立出来，只筛选需要的字段，同时也方便进行最终的筛选
• 给两个表起别名，不省略 as （虽然可以不写），别名有一定的语义，原始表时返回字段不用别名调用
• 连接后再在最外层 SELECT 给出要返回的字段，字段指定来自哪个表
• 最终返回字段加注释
• outer、inner 关键字在常见数据库 SQL 中一般可以省略
• select * from A,B; 不带 where 或者 join 等同于 cross join，带 where 等同于 inner join

semi join

Semi Join，也叫半连接。Semi-join从一个表中返回的行与另一个表中数据行进行不完全联接查询（查找到匹配的数据行就返回，不再继续查找）。

大多数情况下 JOIN ON 和 left semi on 是对等的，但当左表与右表的关联列都存在重复数据时，由于产生笛卡尔积，使用 left join 是低效的，此时可以采用 left semi join。当左表 left semi join 右表时，结果表只能有左表的列，且右表只能在 on 中设置过滤条件，并且当右表有重复数据时，左表只会关联一次。

left semi join 中最后 select 的结果只许出现左表，因为右表只有 join key 参与关联计算了，而 join on 默认是整个关系模型都参与计算了。 left semi join 是 in(keySet) 的关系，遇到右表重复记录，左表会跳过，而 join on 则会一直遍历做 key 内 cross join。

在早期 HIVE 版本中，并不支持 Exist/IN 子查询，而是在 0.5 之后提供了 left semi join 语法。

简单理解，left semi join 只将右表做筛选依据（表），但又不返回的数据（没有又表的列）。【重点】它只返回的是左表的数据，左表在右表有的数据。对比如图：

CROSS JOIN

CROSS JOIN 会让左右表排列组合，产生笛卡尔积，效果如图示：

场景总结

从实际业务来看，最常用的就是 left join，以左表为基础进行连接，即在一个确定的数据范围来分析这部分数据的性质。

各种类别的 join 教程后续将结合案例进行讲解，更多连接方式可参见SQL JOIN 逻辑。