The jOOQ User Manual SQL building Table expressions TABLESAMPLE

previous : next

TABLESAMPLE

Supported by ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The SQL standard specifies a TABLESAMPLE clause that can be used to extract a sample percentage or number of rows from a given table.

jOOQ supports the following sample size types:

• PERCENT using TABLE.tablesample(n).percent()
• ROWS using TABLE.tablesample(n).rows()

As well as the following sample methods:

• BERNOULLI using TABLE.tablesampleBernoulli(n)
• SYSTEM using TABLE.tablesampleBernoulli(n)

For repeatable results, an optional repeatable(seed)clause can be added.

Dialect support

This example using jOOQ:

select(BOOK.ID).from(BOOK.tablesample(10))

Translates to the following dialect specific expressions:

Aurora Postgres, DB2, Hana, Postgres, Trino

SELECT BOOK.ID
FROM BOOK TABLESAMPLE BERNOULLI (10)

BigQuery

SELECT BOOK.ID
FROM BOOK TABLESAMPLE SYSTEM (10 PERCENT)

ClickHouse, Databricks, SQLDataWarehouse, SQLServer, Snowflake, Spanner, Teradata, Vertica

SELECT BOOK.ID
FROM BOOK TABLESAMPLE (10)

DuckDB

SELECT BOOK.ID
FROM BOOK USING SAMPLE (10)

Oracle

SELECT BOOK.ID
FROM BOOK SAMPLE (10)

ASE, Access, Aurora MySQL, CockroachDB, Exasol, Firebird, H2, HSQLDB, Informix, MariaDB, MemSQL, MySQL, Redshift, SQLite, Sybase, YugabyteDB

/* UNSUPPORTED */

The jOOQ User Manual SQL building Table expressions TABLESAMPLE

previous : next