如何改写SQL查询解决左外连接被WHERE条件过滤无匹配记录的问题

问题原因 #

你当前的写法会将LEFT JOIN等效转换为INNER JOIN：左连接后没有匹配到PARTBINLOC记录的行中，PARTBINLOC.BRANCHID值为NULL，WHERE子句的PARTBINLOC.BRANCHID = :BID条件会过滤掉所有NULL值行，导致符合要求的基础数据也被剔除。

解决方案 #

把分支ID的筛选条件从WHERE子句移动到PARTBINLOC的左连接关联条件中即可，修改后的SQL如下：

SELECT 
  SCHEDULEPARTS.SCHEDULEID,
  SCHEDULEPARTS.LINE_NO,
  SCHEDULEPARTS.PARTID,
  SCHEDULEPARTS.QUANTITY,
  SCHEDULEPARTS.PRINT,
  PART.PART_NUMBER,
  PART.DESCRIPTION AS PART,
  PART.PRICE,
  PARTBINLOC.BIN_LOCATION
FROM
  SCHEDULEPARTS
  LEFT OUTER JOIN PART ON (SCHEDULEPARTS.PARTID = PART.PARTID)
  LEFT OUTER JOIN PARTBINLOC ON (PART.PARTID = PARTBINLOC.PARTID AND PARTBINLOC.BRANCHID = :BID)
WHERE
  SCHEDULEPARTS.SCHEDULEID = :SID
ORDER BY
  SCHEDULEPARTS.SCHEDULEID,
  SCHEDULEPARTS.LINE_NO

逻辑说明 #

• 关联条件中增加分支筛选后，左连接只会匹配对应分支的库位记录，没有匹配的情况下BIN_LOCATION会自动置为NULL，同时不会过滤SCHEDULEPARTS和PART表的有效数据
• WHERE子句仅保留行程ID筛选条件，完全符合你要求的查询逻辑

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