Postgres三表JOIN时能否按两张关联表的共有列混合排序？

问题原因 #

你当前排序不符合预期的核心原因是：ORDER BY 3,5 ASC 逻辑是优先按第3列（product_a_price）排序，所有type为b的关联记录，product_a_price 取值为NULL，PostgreSQL默认NULL值排序优先级低于所有非NULL值，因此所有A类产品会全部排在B类产品之前。

解决方案 #

只需要将两个子表的price字段合并为统一的排序维度即可，使用COALESCE函数就能实现，调整后的查询语句如下：

SELECT
    items.id AS item_id,
    products_a.name AS product_a_name,
    products_a.price AS product_a_price,
    products_b.name AS product_b_name,
    products_b.price AS product_b_price
FROM items
FULL JOIN products_a ON items.product_id = products_a.id
FULL JOIN products_b ON items.product_id = products_b.id
-- 合并两个price字段作为统一排序依据
ORDER BY COALESCE(products_a.price, products_b.price) ASC;

逻辑说明 #

COALESCE函数会返回参数列表中第一个非NULL的值：

• 对type为a的记录，取products_a.price作为排序值
• 对type为b的记录，取products_b.price作为排序值
  执行后排序结果正好符合你预期的10、20、50、100的升序顺序。

如果需要更高的可读性、避免极端情况下两个price都非NULL的排序异常，也可以换成基于type判断的CASE WHEN写法，效果和上述方案一致：

ORDER BY 
  CASE 
    WHEN items.type = 'a' THEN products_a.price 
    WHEN items.type = 'b' THEN products_b.price 
  END ASC

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