如何查询含匹配与无匹配关联的行？SQL关联查询问题

可行方案一：子查询预筛选关联关系 #

先通过子查询筛选出所有关联了directory类型分类的资产-分类ID映射，再与资产表做LEFT JOIN，确保每个资产仅返回一条记录：

SELECT a.id AS "assetId", dir_cat.id AS "categoryId"
FROM asset a
LEFT JOIN (
    SELECT ac.asset_id, c.id
    FROM asset_category ac
    INNER JOIN category c 
        ON ac.category_id = c.id 
        AND c.ctype = 'directory'
) dir_cat ON a.id = dir_cat.asset_id

可行方案二：聚合函数去重 #

基于你最初的Query A，通过GROUP BY按资产ID分组，用MAX()函数提取有效分类ID（自动忽略null值）：

SELECT a.id AS "assetId", MAX(c.id) AS "categoryId"
FROM asset a
LEFT JOIN asset_category ac ON ac.asset_id = a.id
LEFT JOIN category c 
    ON c.id = ac.category_id 
    AND c.ctype = 'directory'
GROUP BY a.id

结果验证 #

两种方案都会返回你期望的输出：

assetId | categoryId
1       | 2
2       | null

原查询问题分析 #

• Query A：两次LEFT JOIN会拉取资产关联的所有分类，非directory类型的分类会导致categoryId为null，因此asset1会出现两条记录（一条对应null，一条对应有效分类ID）。
• Query B：使用INNER JOIN筛选directory分类时，会直接过滤掉没有匹配该类型分类的资产（如asset2），导致丢失记录。

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