WITH和JOIN的区别

WITH 和 JOIN 的核心区别

WITH 和 JOIN 不是同一类东西，所以不能简单理解成“WITH 相比 JOIN 有什么优势”。
更准确的比较是：

JOIN：负责把两张表按条件关联起来
WITH：负责给一段子查询起名字，方便后面引用

也就是说，WITH 不是 JOIN 的替代品。真正做关联的，还是 left join、inner join 这些语法。

重点：WITH 对比的不是 JOIN，而是 left join (...) 里面那一大坨子查询写法。

一个最简单的例子

假设有一张日志表 log_table：

user_id	log_type
A	3
A	3
A	2
B	3
其中：

log_type = 3 表示曝光
log_type = 2 表示点击

现在想看每个用户的曝光数和点击数。

不用 WITH 的写法

这是你平时熟悉的写法：两个子查询直接放在 from 和 left join 里面。

select
    e.user_id,
    e.exp_cnt,
    c.click_cnt
from
(
    select
        user_id,
        count(1) as exp_cnt
    from log_table
    where log_type = 3
    group by user_id
) e
left join
(
    select
        user_id,
        count(1) as click_cnt
    from log_table
    where log_type = 2
    group by user_id
) c
on e.user_id = c.user_id;

这段 SQL 的逻辑是：

先算曝光数据 e
再算点击数据 c
最后用 user_id 做 left join

用 WITH 的写法

同样的逻辑，也可以用 WITH 写：

with exp_base as (
    select
        user_id,
        count(1) as exp_cnt
    from log_table
    where log_type = 3
    group by user_id
),
click_base as (
    select
        user_id,
        count(1) as click_cnt
    from log_table
    where log_type = 2
    group by user_id
)
select
    e.user_id,
    e.exp_cnt,
    c.click_cnt
from exp_base e
left join click_base c
on e.user_id = c.user_id;

这段 SQL 的逻辑还是一样：

先算曝光数据 exp_base
再算点击数据 click_base
最后用 user_id 做 left join

区别只是：WITH 先把两个子查询单独命名了。

两种写法本质一样

不用 WITH 时：

from (曝光子查询) e
left join (点击子查询) c
on e.user_id = c.user_id

用 WITH 时：

with exp_base as (曝光子查询),
click_base as (点击子查询)
select ...
from exp_base e
left join click_base c
on e.user_id = c.user_id

它们最终都在做同一件事：

曝光结果 left join 点击结果

所以不要把 WITH 理解成新的关联方式。它只是把子查询从 join 里面拿出来，先起一个名字。

WITH 的优势在哪里？

WITH 的优势主要是让复杂 SQL 更好读，不是让 JOIN 消失。

1. 每块数据有名字

不用 WITH 时，看到的是：

(select ... where log_type = 3) e
(select ... where log_type = 2) c

用 WITH 时，看到的是：

exp_base：曝光数据
click_base：点击数据

名字会让 SQL 的意图更明显。

2. 可以先单独检查每块数据

比如复杂 SQL 里，你可以先只看：

select *
from exp_base;

确认曝光口径没问题，再看：

select *
from click_base;

确认点击口径没问题。

实际排查数据问题时，WITH 的好处就是可以把大 SQL 拆成几块检查，不容易一眼糊住。

3. 最后一层只负责关联

复杂报表里经常会有很多口径：

exp_base：曝光口径
click_base：点击口径
charge_base：计费口径
conv_base：转化口径

如果全塞在 left join (...) 里面，最后 SQL 会很长。
用 WITH 可以把前面的数据准备和最后的关联分开：

WITH 区域：准备每块数据
最后 SELECT：负责 join 和汇总

4. 避免子查询嵌套太深

如果 SQL 只有两个简单子查询，用不用 WITH 都行。
但如果子查询里还有子查询，或者同一段逻辑要复用，WITH 会更清楚。

什么时候不用 WITH？

如果 SQL 很短，或者只有一两个简单关联，直接用你熟悉的写法完全没问题：

select ...
from
(
    select ...
) e
left join
(
    select ...
) c
on ...

这种写法更直接，也更容易看到 left join 的结构。

结论：小 SQL 可以不用 WITH；SQL 变长、口径变多、子查询变复杂时，再用 WITH 会更自然。

对这次反过滤点击 SQL 的理解

这次需求本质上是：

曝光数据：从 spam 表里筛 log_type = 3，得到 search_id + ideaid
点击数据：从 spam 表里筛 log_type = 2，得到 search_id + ideaid + click
最终关联：用曝光的 search_id + ideaid 去 left join 点击
目的：看这些曝光对应的点击有没有进入反过滤日志

如果用普通 left join 写，就是：

select
    e.search_id,
    e.ideaid,
    c.click
from
(
    select
        cast(search_id as string) as search_id,
        cast(idea_id as string) as ideaid
    from fc_nad.nativeads_ods_als_spam
    where log_type = 3
    group by 1,2
) e
left join
(
    select
        cast(search_id as string) as search_id,
        cast(idea_id as string) as ideaid,
        count(1) as click
    from fc_nad.nativeads_ods_als_spam
    where log_type = 2
    group by 1,2
) c
on e.search_id = c.search_id
and e.ideaid = c.ideaid;

如果用 WITH 写，就是：

with exp_base as (
    select
        cast(search_id as string) as search_id,
        cast(idea_id as string) as ideaid
    from fc_nad.nativeads_ods_als_spam
    where log_type = 3
    group by 1,2
),
click_base as (
    select
        cast(search_id as string) as search_id,
        cast(idea_id as string) as ideaid,
        count(1) as click
    from fc_nad.nativeads_ods_als_spam
    where log_type = 2
    group by 1,2
)
select
    e.search_id,
    e.ideaid,
    c.click
from exp_base e
left join click_base c
on e.search_id = c.search_id
and e.ideaid = c.ideaid;

两者表达的是同一个意思。

读 WITH SQL 的顺序

看到 WITH 写法时，可以按这个顺序读：

1. 先看最后 from 谁开始
2. 再看 join 了谁
3. 再看 on 条件是什么
4. 最后回头看 WITH 里每个临时表怎么来的

因为真正决定表和表怎么关联的，不是 WITH，而是最后的：

from ...
join ...
on ...

核心记忆

WITH 负责“命名子查询”
JOIN 负责“关联两张表”
WITH 不是 JOIN 的替代品
WITH 的优势是让复杂 SQL 分块、命名、好检查
小 SQL 不一定需要 WITH
真正的关联关系一定要看 from / join / on

更新: 2026-06-02 23:37:00 CST

MarkZ

探索