ANTI JOIN（反连接）
适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版
关系代数定义了一个 ANTI JOIN（反连接）操作，遗憾的是它尚未进入标准 SQL（尚未），尽管使用 NOT EXISTS 谓词很容易模拟它。 与 SEMI JOIN（半连接）不同，不建议使用 NOT IN 谓词来模拟 ANTI JOIN，因为这可能会在存在 NULL 值时出错，这是一个非常微妙且难以发现的错误。
jOOQ 提供了一个方便的 LEFT ANTI JOIN（左反连接）运算符来匹配关系代数语义。 以下查询将生成所有没有书籍的作者
SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME FROM AUTHOR WHERE NOT EXISTS ( SELECT * FROM BOOK WHERE BOOK.AUTHOR_ID = AUTHOR.ID )
create.select(
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME
)
.from(AUTHOR)
.leftAntiJoin(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
.fetch();
结果可能如下所示，即我们的数据库中可能有一位作者 Jane Austen，但我们还没有关于她的任何书籍
+------------+-----------+ | FIRST_NAME | LAST_NAME | +------------+-----------+ | Jane | Austen | +------------+-----------+
当然，您也可以在 jOOQ 中使用 NOT EXISTS 来形成等效的查询。 也可以通过使用 LEFT JOIN（左外连接） 和一个 NULL 谓词在反连接表的外部主键上，并将其置于 ON 子句之外，来实现 ANTI JOIN 语义，尽管这可能有点深奥且难以阅读
SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME FROM AUTHOR LEFT JOIN BOOK ON BOOK.AUTHOR_ID = AUTHOR.ID WHERE BOOK.ID IS NULL
create.select(
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME)
.from(AUTHOR)
.leftJoin(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
.where(BOOK.ID.isNull())
.fetch();
考虑 LEFT JOIN（左外连接）示例结果
+------------+-----------+--------------+ | FIRST_NAME | LAST_NAME | TITLE | +------------+-----------+--------------+ | George | Orwell | 1984 | | George | Orwell | Animal Farm | | Paulo | Coelho | O Alquimista | | Paulo | Coelho | Brida | <-- Reject all of the above where we have BOOK.ID IS NOT NULL | Jane | Austen | | <-- Keep only this row, where BOOK.ID IS NULL +------------+-----------+--------------+
select(AUTHOR.ID).from(AUTHOR).leftAntiJoin(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
翻译成以下特定方言的表达式
ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, CockroachDB, DB2, Derby, Exasol, Firebird, H2, HSQLDB, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica, YugabyteDB
SELECT AUTHOR.ID FROM AUTHOR WHERE NOT EXISTS ( SELECT 1 FROM BOOK WHERE BOOK.AUTHOR_ID = AUTHOR.ID )
ClickHouse、Databricks
SELECT AUTHOR.ID
FROM AUTHOR
LEFT ANTI JOIN BOOK
ON BOOK.AUTHOR_ID = AUTHOR.ID
DuckDB
SELECT AUTHOR.ID
FROM AUTHOR
ANTI JOIN BOOK
ON BOOK.AUTHOR_ID = AUTHOR.ID
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意听到您的意见！