如何通过最优SQL查询获取带回答状态的全量问题列表？

现有表结构 #

question表 #

Id    |  name
-------------------
1         | question_1
2         | ...
3         | ...
4         | ...

response表 #

ID       |  name       |  question_id
------------------------------------
1         | response_1  |     1
2         | ...         |     2
3         | ...         |     4  
4         | ...         |     4

需求 #

获取所有question的列表，新增status列——存在对应回答时为true，无回答时为false，期望结果如下：

question_Id  |  status
-------------------
1            | true
2            | true
3            | false
4            | true

最优查询方案 #

方法1：使用EXISTS子查询（推荐） #

EXISTS子查询会在找到匹配的回答记录后立即终止检索，无需统计所有匹配项，在数据量较大时性能更优：

SELECT 
    q.Id AS question_Id,
    EXISTS(SELECT 1 FROM response r WHERE r.question_id = q.Id) AS status
FROM question q;

方法2：LEFT JOIN结合条件判断 #

如果需要兼容部分特殊数据库，也可以用左连接的方式实现：

SELECT 
    q.Id AS question_Id,
    CASE WHEN r.question_id IS NOT NULL THEN true ELSE false END AS status
FROM question q
LEFT JOIN response r ON q.Id = r.question_id
GROUP BY q.Id;

或者通过统计匹配数量来判断：

SELECT 
    q.Id AS question_Id,
    COUNT(r.question_id) > 0 AS status
FROM question q
LEFT JOIN response r ON q.Id = r.question_id
GROUP BY q.Id;

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