我太懂这种踩坑的感觉了！你想要查询那些从未被作为Relation表中other字段引用的User记录，但当前用Criteria API写的代码生成了有问题的SQL——不仅出现了没必要的cross join，还搞出了not in (null)这种完全无效的条件，导致查询根本达不到预期效果对吧？

问题根源分析 #

1. in表达式用法错误：你当前的代码里只给builder.in()传了子查询，但没有指定外层要对比的字段（也就是User表的id），JPA没法自动匹配，就乱做了交叉连接，还生成了奇怪的not in (null)部分。
2. not in的null陷阱：哪怕你写法正确，如果子查询返回的结果里包含null，not in整个条件都会失效——因为SQL里任何值和null做比较都会返回unknown，最终这部分条件会过滤掉所有数据。

两种可行的修复方案 #

方案1：修正not in的写法（明确对比字段）

把外层User的id和子查询返回的other_id明确关联起来，代码调整如下：

return (root, query, builder) -> {
    // 子查询：获取当前user对应的所有Relation.other的id
    Subquery<Long> subQuery = query.subquery(Long.class);
    Root<Relation> subRoot = subQuery.from(Relation.class);
    
    subQuery.select(subRoot.get("other").get("id"))
            .where(builder.equal(subRoot.get("user"), user));
    
    // 明确指定：User的id NOT IN 子查询结果
    return builder.not(root.get("id").in(subQuery));
};

方案2：用not exists替代not in（更推荐）

not exists完全不受子查询中null的影响，逻辑也更直观——直接判断“不存在关联当前user且other指向该User的Relation记录”，代码如下：

return (root, query, builder) -> {
    Subquery<Relation> subQuery = query.subquery(Relation.class);
    Root<Relation> subRoot = subQuery.from(Relation.class);
    
    // 子查询的匹配条件：Relation的other是当前外层User，且Relation的user是指定user
    Predicate existsCondition = builder.and(
            builder.equal(subRoot.get("other").get("id"), root.get("id")),
            builder.equal(subRoot.get("user"), user)
    );
    
    // 外层条件：不存在满足上述条件的Relation记录
    return builder.not(builder.exists(subQuery.where(existsCondition)));
};

效果说明 #

调整后生成的SQL就会正常了：

• 方案1会生成user.id not in (select other_id from relation where user_id = ?)的有效条件；
• 方案2会生成not exists (select 1 from relation where other_id = user.id and user_id = ?)的高效查询，性能上也更优。

备注：内容来源于stack exchange，提问作者littleAlien