PostgreSQL中如何仅筛选两表关联后差值大于0的结果？

先看你SQL里的明显问题 #

你写的SELECT语句中引用了response.amount_inserted，但你描述的response表字段是amount_added——如果这是笔误，建议统一修正为response.amount_added，避免字段混淆。

导致WHERE条件无返回的可能原因及解决办法 #

1. 数据实际无满足条件的行
   先执行不带WHERE的查询并按差值排序，确认是否真的存在response.amount_added < request.amount_requested的行：

   SELECT request.order_id,
          request.amount_requested,
          response.amount_added,
          request.amount_requested - response.amount_added AS amount_remaining
   FROM request
   INNER JOIN response ON request.order_id = response.order_id
   ORDER BY amount_remaining DESC;
   

   如果排序后所有amount_remaining都是≤0的，那WHERE条件自然不会返回结果。

2. 浮点精度误差
   如果amount_requested和amount_added是浮点类型（如FLOAT、REAL），直接用>比较可能因精度丢失导致本该成立的条件失效（比如10.0和9.999999999999998会被判定为相等）。这种情况可以用差值的绝对值来过滤：

   SELECT request.order_id,
          request.amount_requested,
          response.amount_added,
          request.amount_requested - response.amount_added AS amount_remaining
   FROM request
   INNER JOIN response ON request.order_id = response.order_id
   WHERE ABS(request.amount_requested - response.amount_added) > 0.000001; -- 阈值根据业务精度调整
   

3. NULL值干扰
   如果任一字段存在NULL值，request.amount_requested > response.amount_added的结果会是NULL，被WHERE条件过滤。可以添加NULL排除条件：

   SELECT request.order_id,
          request.amount_requested,
          response.amount_added,
          request.amount_requested - response.amount_added AS amount_remaining
   FROM request
   INNER JOIN response ON request.order_id = response.order_id
   WHERE request.amount_requested IS NOT NULL
     AND response.amount_added IS NOT NULL
     AND request.amount_requested > response.amount_added;
   

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