PostgreSQL中如何用IN子句将多列转为元组匹配子查询结果

问题背景 #

有两个PostgreSQL表table_j和table_r，二者包含共同字段：a, b, c, d。需要完成以下操作：

1. 从table_r中提取满足date > '2022-05-01'且code='123456'条件的a,b,c,d唯一元组，执行语句：

select distinct(a, b, c, d) from table_r where date > '2022-05-01' and code='123456'

2. 从table_j中提取与上述查询结果中a、b、c、d字段值匹配的记录，尝试执行语句：

select a, b, c, d, e 
from table_j 
where (a, b, c, d) in
    ( select distinct(a, b, c, d) from table_r where date > '2022-05-01' and code='123456' )
order by a, b, c, d;

但触发报错：

ERROR:  the subquery has too few columns

LINE 1: e from table_j where (a, b, c, d) in
                                          ^

问题根源：子查询里的distinct(a, b, c, d)返回的是单列的复合元组，而外层的(a, b, c, d)被解析为四个独立字段，两边列数不匹配导致报错。

可行解决方案 #

方案1：修改子查询返回多列而非复合元组 #

把子查询中的distinct(a, b, c, d)改为distinct a, b, c, d，让子查询直接返回四列，与外层的四字段条件完全对齐：

select a, b, c, d, e 
from table_j 
where (a, b, c, d) in
    ( select distinct a, b, c, d from table_r where date > '2022-05-01' and code='123456' )
order by a, b, c, d;

方案2：将外层条件转为复合元组匹配 #

如果要保留子查询返回复合元组的写法，外层用ROW()构造函数把四个字段包装成单个复合元组，让两边类型一致：

select a, b, c, d, e 
from table_j 
where ROW(a, b, c, d) in
    ( select distinct(a, b, c, d) from table_r where date > '2022-05-01' and code='123456' )
order by a, b, c, d;

方案3：改用JOIN实现（大数据量更高效） #

对于数据量较大的场景，JOIN的性能通常优于IN子查询，写法如下：

select j.a, j.b, j.c, j.d, j.e
from table_j j
inner join (
    select distinct a, b, c, d from table_r where date > '2022-05-01' and code='123456'
) r 
on j.a = r.a 
and j.b = r.b 
and j.c = r.c 
and j.d = r.d
order by j.a, j.b, j.c, j.d;

如果需要去重（避免table_j中存在重复匹配记录），可以在select后加上distinct。

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