LEFT JOIN查询异常：如何保留未关联ID并筛选指定idtype？

表结构 #

NAMES表 #

id     fname    Tax  
1      jack     56982
1000   Tim      32165
2321   Andrew   98956
231    Jim      11215

NAMES_VERIFICATIONS表 #

id     idtype  iddata
1      tax      56982
1      passport 12365
2321   tax      98956
2321   passport 65656

需求与问题 #

需要查询NAMES表的所有记录，同时关联NAMES_VERIFICATIONS表中idtype='tax'对应的iddata字段，预期输出包含所有NAMES记录，未匹配的iddata显示为NULL：

预期输出 #

NAMES.id   NAMES.fname  NAMES.TAX   NAMES_VERIFICATIONS.iddata
1          jack            56982     56982     
1000       Tim            32165      NULL
2321       Andrew          98956     98956
231        Jim             11215      NULL

但使用以下查询语句后，仅返回了匹配idtype='tax'的记录，丢失了NAMES表中id为1000和231的未匹配记录：

错误查询语句 #

Select Names.id,Names.fname,NAMES.TAX,NAMES_VERIFICATIONS.iddata
FROM Names
LEFT JOIN NAMES_VERIFICATIONS 
ON Names.id = NAMES_VERIFICATIONS.id
WHERE NAMES_VERIFICATIONS.idtype = 'tax'
Order by Names.id

错误输出结果 #

NAMES.id   NAMES.fname  NAMES.TAX   NAMES_VERIFICATIONS.iddata
1          jack            56982     56982     
2321       Andrew          98956     98956

原因与解决方案 #

问题原因 #

LEFT JOIN后，未匹配NAMES_VERIFICATIONS的记录中，该表的所有字段值为NULL。WHERE子句中的NAMES_VERIFICATIONS.idtype = 'tax'会过滤掉这些NULL值的记录，导致丢失未匹配的NAMES数据。

正确写法 #

方法一：将过滤条件移至JOIN的ON子句（推荐）

在关联时就只匹配idtype='tax'的记录，未匹配的NAMES记录会被保留：

SELECT Names.id, Names.fname, NAMES.TAX, NAMES_VERIFICATIONS.iddata
FROM Names
LEFT JOIN NAMES_VERIFICATIONS 
  ON Names.id = NAMES_VERIFICATIONS.id
  AND NAMES_VERIFICATIONS.idtype = 'tax'
ORDER BY Names.id

方法二：在WHERE子句中保留NULL记录

通过判断idtype为NULL的情况，保留未匹配的记录：

SELECT Names.id, Names.fname, NAMES.TAX, NAMES_VERIFICATIONS.iddata
FROM Names
LEFT JOIN NAMES_VERIFICATIONS 
  ON Names.id = NAMES_VERIFICATIONS.id
WHERE NAMES_VERIFICATIONS.idtype = 'tax' 
   OR NAMES_VERIFICATIONS.idtype IS NULL
ORDER BY Names.id

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