[笔记] SQL join/on/ROW_NUMBER

目录

• inner join
• left/right outer join
• full outer join
• cross join (或不指定 join 关键字)
• on 条件用于过滤
• ROW_NUMBER

inner join

inner join 可以简写为 join

产生两张表满足 on 条件的记录的交集

select
    *
from
    table_a
join
    table_b
on
    table_a.id = table_b.id

on 条件也可以是不等式

select
    *
from
    table_a
join
    table_b
on
    table_a.id != table_b.id

不等于这种方式比较少用

left/right outer join

left/right outer join 可以简写为 left/right join

以 left join 为例子，左表必然出现在结果中，
对于右表存在满足 on 条件的记录的，取交集，和 inner join 一样，
对于右表不存在满足 on 条件的记录的，取左表部分 + 补全 null

select
    *
from
    table_a
left join
    table_b
on
    table_a.id = table_b.id

right join 则相反

full outer join

full outer join 可以简写为 full join，
对于满足 on 条件的记录的，取交集，和 inner join 一样，
对于右表不存在满足 on 条件的记录的，取左表部分 + 补全 null，和 left join 一样，
对于左表不存在满足 on 条件的记录的，取右表部分 + 补全 null，和 right join 一样

select
    *
from
    table_a
full join
    table_b
on
    table_a.id = table_b.id

相当于 left join 和 right join 的结合

cross join (或不指定 join 关键字)

把 table_a 和 table_b 的数据进行一个 N*M 的组合

select
    *
from
    table_a
cross join
    table_b

或者

select
    *
from
    table_a, table_b

不需要加 on 条件，加上会报错

on 条件用于过滤

on 条件除了做链接用，也可以做筛选用，

对于 inner join 过滤条件放在 on 还是 where 效果一样，而 outer join 效果不一样，

比如 left join 的左表有记录 a 而右表有记录 b 并且能满足 on 条件，如果是在 where 过滤比如

where table_right.name != 'xxxx'

那么 a 记录和 b 记录都不会出现在结果中，因为是先 join 再过滤，

而如果是在 on 过滤比如

on table_right.name != 'xxxx'

那么 a 记录依然出现在结果中，只是右表部分用 null 补全，因为先过滤掉 b 记录再 left join

ROW_NUMBER

ROW_NUMBER() OVER(
    [PARTITION BY column_1, column_2,…]
    [ORDER BY column_3,column_4,…]
)

按字段分组，然后组内排序，并按组内顺序添加组内行号列

比如下面的 SQL

SELECT
    group_id,
    created_time,
    Row_Number() OVER (partition by group_id ORDER BY created_time desc) row_id
FROM 
    table_a

数据按 group_id 分组，组内按 created_time 排序，组内排序后的行号作为新字段 row_id 添加到记录中

 group_id |    created_time     | row_id
----------+---------------------+--------
 group_1  | 2021-08-06 11:13:32 |      1
 group_1  | 2021-08-06 11:12:32 |      2
 group_1  | 2021-08-06 11:11:32 |      3
 group_2  | 2021-08-06 12:13:32 |      1
 group_2  | 2021-08-06 12:12:32 |      2
 group_3  | 2021-08-06 13:13:32 |      1