DISTINCT谓词
适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版
一些数据库支持 DISTINCT 谓词，它是一个方便的，NULL 安全的 比较谓词。 使用 DISTINCT 谓词，可以假定以下真值表
-
[ANY] IS DISTINCT FROM NULL产生TRUE -
[ANY] IS NOT DISTINCT FROM NULL产生FALSE -
NULL IS DISTINCT FROM NULL产生FALSE -
NULL IS NOT DISTINCT FROM NULL产生TRUE
例如，您可以比较两个字段的独特性，忽略任何一个字段可能是 NULL 的事实，这可能会导致有趣的结果。 jOOQ 支持这种方式
TITLE IS DISTINCT FROM SUB_TITLE TITLE IS NOT DISTINCT FROM SUB_TITLE
BOOK.TITLE.isDistinctFrom(BOOK.SUB_TITLE) BOOK.TITLE.isNotDistinctFrom(BOOK.SUB_TITLE)
方言支持
此示例使用 jOOQ
AUTHOR.FIRST_NAME.isDistinctFrom(AUTHOR.LAST_NAME)
翻译成以下特定方言的表达式
ASE, Exasol, SQLDataWarehouse, Vertica
NOT EXISTS ( SELECT AUTHOR.FIRST_NAME x INTERSECT SELECT AUTHOR.LAST_NAME x )
Aurora MySQL, MariaDB, MemSQL, MySQL
(NOT(AUTHOR.FIRST_NAME <=> AUTHOR.LAST_NAME))
Aurora Postgres, BigQuery, CockroachDB, DB2, Databricks, DuckDB, Firebird, H2, HSQLDB, Postgres, Redshift, SQLServer, Snowflake, Trino, YugabyteDB
AUTHOR.FIRST_NAME IS DISTINCT FROM AUTHOR.LAST_NAME
ClickHouse
arrayUniq(ARRAY(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)) = 2
Derby
NOT EXISTS ( SELECT AUTHOR.FIRST_NAME x FROM SYSIBM.SYSDUMMY1 INTERSECT SELECT AUTHOR.LAST_NAME x FROM SYSIBM.SYSDUMMY1 )
Hana, Sybase
NOT EXISTS ( SELECT AUTHOR.FIRST_NAME x FROM SYS.DUMMY INTERSECT SELECT AUTHOR.LAST_NAME x FROM SYS.DUMMY )
Informix
NOT EXISTS (
SELECT AUTHOR.FIRST_NAME x
FROM (
SELECT 1 AS dual
FROM systables
WHERE (tabid = 1)
) AS dual
INTERSECT
SELECT AUTHOR.LAST_NAME x
FROM (
SELECT 1 AS dual
FROM systables
WHERE (tabid = 1)
) AS dual
)
Oracle
decode(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, 1, 0) = 0
SQLite
(AUTHOR.FIRST_NAME IS NOT AUTHOR.LAST_NAME)
Teradata
NOT EXISTS (
SELECT AUTHOR.FIRST_NAME x
FROM (
SELECT 1 AS "dual"
) AS "dual"
INTERSECT
SELECT AUTHOR.LAST_NAME x
FROM (
SELECT 1 AS "dual"
) AS "dual"
)
访问
/* UNSUPPORTED */
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意听取您的意见！