使用SELECT时触发42601语法错误，子查询ON子句问题求助

你的子查询核心问题是错误使用了ON关键字：

• ON仅能用于多表JOIN操作中定义关联条件，而你这里是单表查询的子查询，关联主表与子查询表的条件应该用WHERE来声明。

修正方案 #

场景1：每辆车仅对应一张图片 #

直接将ON替换为WHERE即可，修正后的SQL如下：

SELECT
    c.id,
    c.model,
    c.description,
    c.environment,
    c.equipment,
    c.ps,
    c.tuv,
    c.cover,
    c.brand,
    c.price,
    c.engine,
    c.fuel,
    c.firstregistration,
    c.km,
    c.color,
    c.video,
    c.type,
    c.show,
    c.sit_place,
    (SELECT ce.image FROM cars_images ce WHERE c.id = ce.car_id) as images
FROM cars c
GROUP BY c.id
LIMIT 100

场景2：每辆车对应多张图片 #

如果单辆车关联多张图片，上面的标量子查询会因返回多行报错，此时可以用array_agg将所有图片聚合为数组：

SELECT
    c.id,
    c.model,
    c.description,
    c.environment,
    c.equipment,
    c.ps,
    c.tuv,
    c.cover,
    c.brand,
    c.price,
    c.engine,
    c.fuel,
    c.firstregistration,
    c.km,
    c.color,
    c.video,
    c.type,
    c.show,
    c.sit_place,
    (SELECT array_agg(ce.image) FROM cars_images ce WHERE c.id = ce.car_id) as images
FROM cars c
GROUP BY c.id
LIMIT 100

或者改用JOIN+聚合的写法，逻辑更直观：

SELECT
    c.id,
    c.model,
    c.description,
    c.environment,
    c.equipment,
    c.ps,
    c.tuv,
    c.cover,
    c.brand,
    c.price,
    c.engine,
    c.fuel,
    c.firstregistration,
    c.km,
    c.color,
    c.video,
    c.type,
    c.show,
    c.sit_place,
    array_agg(ce.image) as images
FROM cars c
LEFT JOIN cars_images ce ON c.id = ce.car_id
GROUP BY c.id
LIMIT 100

为什么别人的类似子查询能运行？ #

别人的子查询大概率是在内部使用了JOIN语句，比如：

(SELECT ... FROM table_a a JOIN table_b b ON a.id = b.a_id)

这种场景下ON是合法的，因为属于多表关联操作，和你单表查询的子查询逻辑完全不同。

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