Oracle SQL左外连接未显示不匹配数据问题排查

你的SQL存在三个核心问题： #

1. 错误定义了items CTE #

你写的items as (select distinct items from main)是从main表中提取item，这导致这个CTE只包含main里已有的item（item1、item2、item3、item4、item5），完全没用到你提到的独立items表，自然不会出现item6这类不匹配的条目。

2. 关联条件的字符串格式不匹配 #

main表的item字段值是item1、item2这类无空格的格式，而独立items表的items字段值是item 1、item 2这类带空格的格式，直接用i.items = m.items会完全匹配失败，导致即使有对应item也关联不上。

3. 缺少location与items的全组合生成 #

你期望的结果是每个location下都列出所有items，这需要先生成location的唯一集合和items的唯一集合的笛卡尔积，再左关联main表。原SQL没有生成这个全组合，只会返回已有匹配的条目。

修正后的SQL语句 #

先处理字符串匹配问题，再生成全组合后关联main表：

with unique_locations as (
    select distinct location from main
),
unique_items as (
    select distinct items from items  -- 调用独立的items表获取完整item列表
)
select 
    ui.items,
    ul.location,
    m.item,
    m.price
from unique_locations ul
cross join unique_items ui  -- 生成所有location与items的全组合
left join main m 
    on ul.location = m.location 
    and replace(ui.items, ' ', '') = m.item  -- 处理空格差异，若为输入笔误可直接用ui.items = m.item
order by ul.location, ui.items;

关键说明 #

• cross join用来生成每个location和每个item的全组合，确保每个location下都能列出所有items；
• 调用独立的items表获取完整的item列表，包括item6这类main里没有的条目；
• 若items表的空格是输入错误（比如item 1实际应为item1），可去掉replace函数，直接用ui.items = m.item做关联。

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