基于多组键值对查询office实体，现有INTERSECT查询是否有更优方案？

表结构 #

office表 #

office_attributes表 #

需求 #

查询同时拥有键值对a key/value1和another key/value2的office，预期仅返回id为1的office。

现有查询语句 #

SELECT * FROM office o WHERE o.id IN (
    SELECT o.id FROM office o LEFT JOIN office_attributes a WHERE a.key = 'a key' AND a.value = 'value1'
    INTERSECT
    SELECT o.id FROM office o LEFT JOIN office_attributes a WHERE a.key = 'another key' AND a.value = 'value2'
)

更优解决方案 #

方案1：分组统计法（多条件场景优先） #

通过关联属性表筛选符合条件的记录，按office分组后统计满足条件的数量，确保该office同时具备所有指定键值对：

SELECT o.*
FROM office o
JOIN office_attributes a ON o.id = a.office_id
WHERE (a.key = 'a key' AND a.value = 'value1') 
   OR (a.key = 'another key' AND a.value = 'value2')
GROUP BY o.id, o.user_id
HAVING COUNT(DISTINCT a.key) = 2;

说明：如果数据保证每个office的key唯一，可将COUNT(DISTINCT a.key)改为COUNT(*) = 2，进一步提升效率。

方案2：多表关联法（逻辑直观） #

直接将office表与属性表进行两次关联，分别匹配两个键值对，仅保留同时满足条件的记录：

SELECT o.*
FROM office o
JOIN office_attributes a1 ON o.id = a1.office_id 
    AND a1.key = 'a key' AND a1.value = 'value1'
JOIN office_attributes a2 ON o.id = a2.office_id 
    AND a2.key = 'another key' AND a2.value = 'value2';

说明：该写法逻辑清晰，适合条件较少的场景，若office_attributes表在office_id、key、value字段上建有索引，性能会更优。

方案3：EXISTS子查询法（高效简洁） #

用两个EXISTS子查询分别验证office是否具备对应键值对，仅当两个条件都满足时返回结果：

SELECT *
FROM office o
WHERE EXISTS (
    SELECT 1 FROM office_attributes a 
    WHERE a.office_id = o.id AND a.key = 'a key' AND a.value = 'value1'
)
AND EXISTS (
    SELECT 1 FROM office_attributes a 
    WHERE a.office_id = o.id AND a.key = 'another key' AND a.value = 'value2'
);

说明：EXISTS子查询找到匹配记录后会立即停止检索，在大数量级数据下，性能通常优于INTERSECT写法。

原语句优化建议 #

原语句中的LEFT JOIN可改为INNER JOIN，因为我们要筛选的是存在对应属性的office，LEFT JOIN会保留无匹配属性的无效数据，改成INNER JOIN能减少不必要的数据集处理：

SELECT * FROM office o WHERE o.id IN (
    SELECT o.id FROM office o INNER JOIN office_attributes a 
    ON o.id = a.office_id WHERE a.key = 'a key' AND a.value = 'value1'
    INTERSECT
    SELECT o.id FROM office o INNER JOIN office_attributes a 
    ON o.id = a.office_id WHERE a.key = 'another key' AND a.value = 'value2'
)

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