Oracle SQL三表连接查询结果冗余问题咨询

表结构 #

Table_1 #

info_seq | dep_id | dep_value | algorithm
  66551  |   01   |   0.223   |     MAX
  66551  |   02   |   0.311   |     MAX
  66551  |   03   |   0.226   |     MAX
  66551  |   01   |   98.12   |     MIN
  66551  |   02   |   99.99   |     MIN
  66551  |   03   |   96.56   |     MIN

Table_2 #

info_seq | col_seq | dep_id | dep_value 
  66551  |    2021 |    01  |    0.223   
  66551  |    2021 |    01  |    0.213    
  66551  |    2021 |    02  |    0.311      
  66551  |    2021 |    03  |    0.226 
  66551  |    2022 |    01  |    99.14   
  66551  |    2022 |    01  |    98.12    
  66551  |    2022 |    02  |    99.99      
  66551  |    2022 |    03  |    96.56      

Table_3 #

col_seq | param_name | param_pin_num | algorithm
  2021  |     red    |       c001    |       MAX
  2022  |    yellow  |       c002    |       MIN
  2023  |     black  |       c003    |      AVERAGE

原查询SQL #

select a.info_seq, a.dep_id, a.dep_value, a.algorithm, 
        b.col_seq, c.param_name, c.param_pin_num 
 from table_1 a , table_2 b, table_3 c
where a.info_seq = b.info_seq
 and b.col_seq = c.col_seq
 and a.algorithm = c.algorithm 

预期输出 #

info_seq | dep_id | dep_value | algorithm |   col_seq     | param_pin_num 
    66551  |   01   |   0.223   |     MAX   |     2021      |    c001
    66551  |   02   |   0.311   |     MAX   |     2021      |    c001
    66551  |   03   |   0.226   |     MAX   |     2021      |    c001
    66551  |   01   |   98.12   |     MIN   |     2022      |    c002
    66551  |   02   |   99.99   |     MIN   |     2022      |    c002
    66551  |   03   |   96.56   |     MIN   |     2022      |    c002

原查询返回结果过多的核心原因是缺少Table_1与Table_2之间的关键关联条件，加上隐式连接语法导致逻辑不清晰：

• 仅通过info_seq关联Table_1和Table_2，未匹配dep_id和dep_value，导致Table_1的单条记录会与Table_2中同info_seq、同col_seq下的所有dep_id记录匹配。例如Table_2中2021的dep_id01有两条记录（0.223和0.213），都会和Table_1中dep_id01的MAX记录匹配，生成多余重复行。
• 老式隐式连接写法容易遗漏关联条件，增加出错概率。

SELECT 
    a.info_seq, 
    a.dep_id, 
    a.dep_value, 
    a.algorithm, 
    b.col_seq, 
    c.param_name, 
    c.param_pin_num 
FROM table_1 a
INNER JOIN table_3 c 
    ON a.algorithm = c.algorithm
INNER JOIN table_2 b 
    ON a.info_seq = b.info_seq 
    AND a.dep_id = b.dep_id 
    AND a.dep_value = b.dep_value
    AND b.col_seq = c.col_seq

1. 使用显式INNER JOIN：替代隐式连接写法，逻辑更直观，便于后续维护和排查问题。
2. 补充关键关联条件：增加a.dep_id = b.dep_id和a.dep_value = b.dep_value，确保Table_1的记录仅与Table_2中完全匹配的dep_id和dep_value记录关联，过滤掉无关冗余数据。
3. 优化关联顺序：先通过algorithm关联Table_1和Table_3，再关联Table_2，确保col_seq与algorithm的对应关系准确。

执行上述修正后的SQL，即可得到预期的6条结果记录。

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