SQL查询SF骑手并新增判断其是否为本地司机的flag字段方法

问题说明 #

现有两张表riders（骑手表）和drivers（司机表），部分用户同时具备骑手和司机身份，会同时出现在两张表中。

riders表结构示例 #

需求目标 #

查询所有SF地区的骑手，新增flag字段：若该骑手同时是SF地区的司机，flag输出true，否则输出false，最终仅返回user_id和flag两列。 #

原有代码问题说明 #

你编写的SQL存在两个问题：

1. 用了INNER JOIN内连接，只会保留两张表匹配到的用户，无法返回仅存在于骑手表的SF地区用户
2. 'flag' as flag是把flag字段写死为固定字符串flag，无法实现动态判断是否为司机的逻辑

正确实现方案 #

方案1：左连接+条件判断 #

SELECT 
  r.user_id,
  CASE WHEN d.user_id IS NOT NULL THEN 'true' ELSE 'false' END AS flag
FROM riders r
LEFT JOIN drivers d 
  ON r.user_id = d.user_id 
  AND d.City = 'SF' -- 司机的地区过滤条件必须写在JOIN条件中，不能写在WHERE后，否则会过滤掉未匹配的骑手
WHERE r.City = 'SF';

方案2：EXISTS子查询（关联数据量较大时性能更优） #

SELECT
  user_id,
  CASE WHEN EXISTS (
    SELECT 1 FROM drivers d 
    WHERE d.user_id = riders.user_id 
    AND d.City = 'SF'
  ) THEN 'true' ELSE 'false' END AS flag
FROM riders
WHERE City = 'SF';

输出效果 #

两种方案返回结果一致，符合预期：

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