数据库on和where的区别

SQL表关联时，条件放在on后和where后区别

注：测试环境为DataWorks ODPS SQL。
日常学习、工作中，有一些场景涉及筛选一部分数据，例如查看学生数学成绩，此时不关注语文成绩，就可以通过筛选只查看数学成绩（where 科目 = ‘数学’）。有些场景涉及扩展全表的部分数据，例如查看学生信息，并把成绩90分以上学生展示分数信息，就可以通过关联，将90分以上分数做关联，这样既可以查看全部学生信息，又可以看到成绩90以上的分数（学生信息表左连接分数表 on 分数表.分数> 90）。

一、on和where的区别

on：关联条件，根据连接方式不同，影响关联结果（如左连接时，以左表为主，关联条件为假时，仍然展示左表结果，右表展示为空）。
where：筛选条件，根据关联后的中间结果做筛选后，展示最终结果。

二、以左连接为例测试示例

2.0准备工作，创建测试表，写入测试数据

--创建示例表1
create table stu_info(
    stu_id string COMMENT '学号',
    name string comment '姓名',
    age decimal(3,0) comment '年龄'
)
comment '学生信息表'
;
--创建示例表2
create table  score_info(
    stu_id string COMMENT '学号',
    subject string comment '科目',
    score decimal(3,0) comment '分数'
)
comment '学生成绩表'
;
--写入测试数据
-- 表1
INSERT into stu_info
VALUES ('009','张三峰',19)
,('002','宋江',400)
,('003','张无忌',16)
,('004','魏无忌',106)
;
--表2
INSERT into score_info
VALUES ('001','语文',90)
,('002','语文',100)
,('003','语文',30)
,('001','数学',90)
,('002','数学',100)
,('003','数学',89)
;

2.1条件 科目等于数学 放在关联条件中

把条件 科目等于数学 放在关联条件中，执行时，会先进行关联，此时数学科目关联成功，其他科目关联失败，左连接特性，无论关联条件是否为真，左边全部展示，右边展示关联成功数据。

SELECT a.*,b.* from stu_info a      
LEFT join score_info b 
on a.stu_id = b.stu_id
and b.subject = '数学'
;

对应结果：

stu_id name age stu_id2 subject score
002 宋江 400 002 数学 100
003 张无忌 16 003 数学 89
004 魏无忌 106 \N \N \N
009 张三峰 19 \N \N \N

2.2条件 科目等于数学 放在where条件中

把条件 科目等于数学 放在where条件中，执行时，会先进行关联，关联生成中间结果后，剔除科目不等于数学的数据。即最终结果只展示科目为数学的数据。

SELECT a.*,b.* from stu_info a      
LEFT join score_info b 
on a.stu_id = b.stu_id
where b.subject = '数学' 
;

2.21先执行关联条件

SELECT a.*,b.* from stu_info a      
LEFT join score_info b 
on a.stu_id = b.stu_id
-- where b.subject = '数学' 
;

语句2中间结果：

stu_id name age stu_id2 subject score
009 张三峰 19 \N \N \N
002 宋江 400 002 语文 100
002 宋江 400 002 数学 100
003 张无忌 16 003 语文 30
003 张无忌 16 003 数学 89
004 魏无忌 106 \N \N \N

加上where条件后，最终对应结果：

stu_id name age stu_id2 subject score
002 宋江 400 002 数学 100
003 张无忌 16 003 数学 89