多条件Inner Join联表查询意外返回空集，求助问题原因

数据表结构 #

车辆表 #

车辆特征表 #

车辆参数表 #

问题分析 #

你写的多条件WHERE子句存在逻辑错误：

WHERE 
    Feature.Feature = 'ForkThickness' 
    AND CarSpec.Value > 1.25
    AND Feature.Feature = 'RearTires' 
    AND CarSpec.Value = 'Summer Tires'

这段代码要求同一行数据同时满足两个互相矛盾的条件：Feature.Feature既要等于'ForkThickness'又要等于'RearTires'，CarSpec.Value既要大于1.25又要等于'Summer Tires'。但车辆参数表中每一行只对应一个车辆的一个特征，不可能有这样的行存在，所以查询返回空集。

解决方案 #

要筛选同时满足多个不同特征条件的车辆，有两种常用方法：

方法1：多次关联车辆参数表 #

为每个特征条件单独关联一次CarSpec和CarFeature，确保每个条件对应不同的行：

SELECT DISTINCT ca.Id, ca.Name 
FROM Car AS ca
-- 关联ForkThickness条件
INNER JOIN CarSpec AS cs1 ON ca.id = cs1.CarId    
INNER JOIN CarFeature AS cf1 ON cf1.Id = cs1.CarFeatureId    
-- 关联RearTires条件
INNER JOIN CarSpec AS cs2 ON ca.id = cs2.CarId    
INNER JOIN CarFeature AS cf2 ON cf2.Id = cs2.CarFeatureId    
WHERE 
    cf1.Feature = 'ForkThickness' AND cs1.Value > 1.25
    AND cf2.Feature = 'RearTires' AND cs2.Value = 'Summer Tires'

方法2：分组后用HAVING筛选 #

先筛选出符合任一条件的记录，再按车辆分组，确保车辆同时满足所有条件：

SELECT ca.Id, ca.Name 
FROM Car AS ca
INNER JOIN CarSpec AS cs ON ca.id = cs.CarId    
INNER JOIN CarFeature AS cf ON cf.Id = cs.CarFeatureId    
WHERE 
    (cf.Feature = 'ForkThickness' AND cs.Value > 1.25)
    OR (cf.Feature = 'RearTires' AND cs.Value = 'Summer Tires')
GROUP BY ca.Id, ca.Name
-- 确保车辆满足两个条件（符合条件的记录数为2）
HAVING COUNT(DISTINCT cf.Feature) = 2

这两种方法都能正确返回仅符合两个条件的车辆1（VW Golf）。

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