Available in versions: Dev (3.20) | Latest (3.19) | 3.18

XOR

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The exclusive or (XOR) operator is true only if one of the operands is true, not both.

(TITLE = 'Animal Farm' XOR AUTHOR.LAST_NAME = 'Orwell')
BOOK.TITLE.eq("Animal Farm").xor(AUTHOR.LAST_NAME.eq("Orwell"))

Dialect support

This example using jOOQ:

BOOK.TITLE.eq("Animal Farm").xor(AUTHOR.LAST_NAME.eq("Orwell"))

Translates to the following dialect specific expressions:

Access, Aurora MySQL, MariaDB, MySQL

(
  BOOK.TITLE = 'Animal Farm'
  XOR AUTHOR.LAST_NAME = 'Orwell'
)

ASE, DB2, Firebird, SQLDataWarehouse, SQLServer, Sybase, Teradata

CASE
  WHEN BOOK.TITLE = 'Animal Farm' THEN 1
  WHEN NOT (BOOK.TITLE = 'Animal Farm') THEN 0
END <> CASE
  WHEN AUTHOR.LAST_NAME = 'Orwell' THEN 1
  WHEN NOT (AUTHOR.LAST_NAME = 'Orwell') THEN 0
END

Aurora Postgres, BigQuery, CockroachDB, Derby, DuckDB, Exasol, H2, HSQLDB, MemSQL, Postgres, Redshift, SQLite, Snowflake, Trino, Vertica, YugabyteDB

(BOOK.TITLE = 'Animal Farm') <> (AUTHOR.LAST_NAME = 'Orwell')

Hana, Oracle

CASE
  WHEN BOOK.TITLE = 'Animal Farm' THEN TRUE
  WHEN NOT (BOOK.TITLE = 'Animal Farm') THEN FALSE
END <> CASE
  WHEN AUTHOR.LAST_NAME = 'Orwell' THEN TRUE
  WHEN NOT (AUTHOR.LAST_NAME = 'Orwell') THEN FALSE
END

Informix

CASE
  WHEN BOOK.TITLE = 'Animal Farm' THEN CAST('t' AS boolean)
  WHEN NOT (BOOK.TITLE = 'Animal Farm') THEN CAST('f' AS boolean)
END <> CASE
  WHEN AUTHOR.LAST_NAME = 'Orwell' THEN CAST('t' AS boolean)
  WHEN NOT (AUTHOR.LAST_NAME = 'Orwell') THEN CAST('f' AS boolean)
END

(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo