如何编写JOOQ查询以选取在ActiveSubscribers中无关联的Subscription记录

如何编写JOOQ查询语句，select所有Subscription条目，满足ActiveSubscribers.subscriptionId不存在于Subscriptions表中？

涉及的两张表结构如下：

+-------------------+   +----------------------+
|  Subscriptions    |   |  ActiveSubscribers   |
+-------------------+   +----------------------+
| Id   |    Name    |   | Id   | SubcriptionId |
|-------------------|   |----------------------|
| 1    |  Dogs      |   | 1    |   1           |
| 2    |  Cats      |   | 2    |   2           |
| 3    |  Hamsters  |   +----------------------+
+-------------------+

预期查询结果：

+-------------------+
| 3    |  Hamsters  |
+-------------------+

原有代码问题说明 #

你提交的尝试代码有两个明显错误：

• 主查询from指定的表名错误，应该是Subscriptions而非Subscribers
• 子查询from指定的表名错误，应该是ActiveSubscribers而非Subscribers

正确实现方案 #

方案1：使用NOT IN语法（和你原有写法逻辑对齐，修改错误即可） #

List<Subscription> nonActiveSubscriptions = DSL.using(connection)
    .select()
    .from(DSL.table("Subscriptions"))
    .where(DSL.field("Id").notIn(
        DSL.select(DSL.field("ActiveSubscribers.SubcriptionId"))
            .from(DSL.table("ActiveSubscribers"))
    ))
    .fetch()
    .into(Subscription.class);

方案2：使用NOT EXISTS语法（处理空值时更稳妥，性能通常更优） #

List<Subscription> nonActiveSubscriptions = DSL.using(connection)
    .select()
    .from(DSL.table("Subscriptions"))
    .where(DSL.notExists(
        DSL.selectOne()
            .from(DSL.table("ActiveSubscribers"))
            .where(DSL.field("ActiveSubscribers.SubcriptionId").eq(DSL.field("Subscriptions.Id")))
    ))
    .fetch()
    .into(Subscription.class);

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