SELECT * EXCEPT (...)
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A useful extension to the previously mentioned standard SQL SELECT * syntax is the BigQuery inspired * EXCEPT (columns) syntax, which takes all of a projection's columns, except some columns. Just like the asterisk itself, this is mainly useful for ad-hoc querying, but it can also be useful for an occasional jOOQ query.
// Renders a SELECT * statement - Explicit unqualified asterisk
create.select(asterisk().except(BOOK.ID)).from(BOOK).fetch();
// Renders a SELECT BOOK.* statement - Explicit qualified asterisk
create.select(BOOK.asterisk().except(BOOK.ID))
.from(BOOK)
.fetch();
create.select(BOOK.asterisk().except(BOOK.ID), AUTHOR.asterisk().except(AUTHOR.ID))
.from(BOOK, AUTHOR)
.fetch();
If a dialect doesn't support this syntax natively, jOOQ will just expand the syntax for you, explicitly, given the knowledge about meta data in generated code.
Dialect support
This example using jOOQ:
select(asterisk().except(LANGUAGE.ID)).from(LANGUAGE)
Translates to the following dialect specific expressions:
-- ACCESS, ASE, AURORA_MYSQL, AURORA_POSTGRES, COCKROACHDB, DB2, DERBY, EXASOL, FIREBIRD, HANA, HSQLDB, INFORMIX, MARIADB, -- MEMSQL, MYSQL, ORACLE, POSTGRES, REDSHIFT, SQLDATAWAREHOUSE, SQLITE, SQLSERVER, SYBASE, TERADATA, TRINO, VERTICA, -- YUGABYTEDB SELECT LANGUAGE.CD, LANGUAGE.DESCRIPTION FROM LANGUAGE -- BIGQUERY SELECT * EXCEPT (ID) FROM LANGUAGE -- H2 SELECT * EXCEPT (LANGUAGE.ID) FROM LANGUAGE -- SNOWFLAKE SELECT * EXCLUDE (ID) FROM LANGUAGE
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!