无法合并两张表数据：Left Join未得到预期结果求助

你的查询仅返回一条记录，核心问题是将左连接的过滤条件y.id_process = 10放在了WHERE子句中。左连接后，表A中不匹配表B的记录对应的表B字段均为NULL，NULL = 10的判断结果为假，导致这些记录被直接过滤。

同时从预期结果来看，所有记录需要带上表B中id_process=10的那条记录的id_client值（10680），即使id_step不匹配，因此需要调整关联逻辑以满足需求。

select 
    x.id_step,
    COALESCE(y.id_client, 10680) as id_client,
    x.id_workflow,
    x.id_action, 
    CASE WHEN y.is_approved IS NULL THEN 'pending' ELSE y.is_approved::text END as is_approved,
    y.action_date,
    y.action_by 
from nw_adsys_wfx_config as x 
left join nw_adsys_cli_wfx_process as y 
    on x.id_step = y.id_step 
    and y.id_process = 10
    and x.id_workflow = y.id_workflow
where x.id_workflow = 1;

如果表B中id_workflow=1且id_process=10的记录唯一，也可以用CTE先筛选出目标记录，再关联表A，逻辑更清晰：

with target_b as (
    select id_step, id_client, is_approved, action_date, action_by
    from nw_adsys_cli_wfx_process
    where id_workflow = 1 and id_process = 10
)
select 
    x.id_step,
    COALESCE(t.id_client, 10680) as id_client,
    x.id_workflow,
    x.id_action, 
    CASE WHEN t.is_approved IS NULL THEN 'pending' ELSE t.is_approved::text END as is_approved,
    t.action_date,
    t.action_by 
from nw_adsys_wfx_config as x 
left join target_b as t 
    on x.id_step = t.id_step
where x.id_workflow = 1;

1. 将表B过滤条件移至ON子句：左连接时仅匹配表B中符合id_process=10且id_workflow=1的记录，不会过滤表A中不匹配的全量数据。
2. 用COALESCE保证id_client值：确保即使表B无匹配记录，依然返回预期的10680值。
3. 关联id_workflow：限制仅关联同一工作流的记录，避免跨工作流的错误匹配。

内容的提问来源于stack exchange，提问作者ZoZo