表连接后如何筛选满足udf_id=2时value非空的唯一结果

方法一：用NOT EXISTS子查询（推荐，逻辑直观） #

先把原查询里多余的CASE去掉（毕竟WHERE已经限定了只取tB.udf_id='1'的记录），再通过子查询排除那些存在udf_id=2且value为NULL的id：

SELECT DISTINCT
    tA.id AS 'id',
    tB.value AS 'side'
FROM tableA tA
INNER JOIN tableB tB ON tA.id = tB.id
WHERE 
    tB.udf_id = '1'
    AND NOT EXISTS (
        SELECT 1 
        FROM tableB tB_exclude 
        WHERE tB_exclude.id = tA.id 
          AND tB_exclude.udf_id = '2' 
          AND tB_exclude.value IS NULL
    );

方法二：用LEFT JOIN实现筛选 #

通过左连接tB中udf_id=2的记录，然后筛选要么没有这条记录，要么这条记录的value不为NULL：

SELECT DISTINCT
    tA.id AS 'id',
    tB.value AS 'side'
FROM tableA tA
INNER JOIN tableB tB ON tA.id = tB.id
LEFT JOIN tableB tB_check ON tA.id = tB_check.id AND tB_check.udf_id = '2'
WHERE 
    tB.udf_id = '1'
    AND (tB_check.id IS NULL OR tB_check.value IS NOT NULL);

方法三：用GROUP BY+HAVING聚合判断 #

如果习惯用分组的方式，也可以这么写：

SELECT
    tA.id AS 'id',
    MAX(CASE WHEN tB.udf_id = '1' THEN tB.value END) AS 'side'
FROM tableA tA
LEFT JOIN tableB tB ON tA.id = tB.id
GROUP BY tA.id
HAVING 
    -- 保留有udf_id=1记录的行（和原INNER JOIN逻辑一致）
    MAX(CASE WHEN tB.udf_id = '1' THEN tB.value END) IS NOT NULL
    -- 排除存在udf_id=2且value为NULL的行
    AND (MAX(CASE WHEN tB.udf_id = '2' THEN tB.value END) IS NOT NULL OR MAX(CASE WHEN tB.udf_id = '2' THEN tB.value END) IS NULL);

补充说明 #

1. 原查询的CASE完全多余：WHERE tB.udf_id IN ('1')已经把范围锁死在udf_id=1的记录上，直接取tB.value就行，没必要多写判断。
2. 你之前加条件出现重复，是因为直接在WHERE里加udf_id相关条件会让JOIN关联到多条记录，导致同一个id重复出现。用上面几种方法，既能精准过滤，又能保证每个id只返回一行。
3. 三种方法里，NOT EXISTS的写法最容易理解，数据库优化器也通常能给它做很好的性能优化，优先用这个就够了。

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