TABLESAMPLEQuery Techniques
Buy Me A Coffee

Return a random sample of rows from a table efficiently, without scanning and sorting the full table.

Returns a statistical sample of rows from a table using BERNOULLI (row-level random) or SYSTEM (block-level random) methods. Useful for quick approximate counts and sampling large tables. The sample size is approximate, not exact.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✗ Not Supported — Not supported. ORDER BY RAND() LIMIT n works but scans the full table and is very slow on large datasets.
PostgreSQL ✓ Supported 9.5 Two methods: BERNOULLI (true row-level statistical sample — accurate but slower) and SYSTEM (page-level — faster but may have clustering bias). Optional REPEATABLE(seed) for reproducible results. Custom sampling methods can be added via extensions (e.g., tsm_system_rows).
SQL Server ✓ Supported 2005 TABLESAMPLE SYSTEM(n PERCENT) or TABLESAMPLE (n ROWS). Only the SYSTEM method (page-level). Optional REPEATABLE(seed).
Oracle ✓ Supported 8i Non-standard SAMPLE clause syntax (not called TABLESAMPLE). SAMPLE(pct) is row-level (like BERNOULLI); SAMPLE BLOCK(pct) is page-level (like SYSTEM). Optional SEED(n) for reproducibility.
SQLite ✗ Not Supported — Not supported. Workaround: random rowid selection.

Details

PostgreSQL is the only engine offering both BERNOULLI (true row-level) and SYSTEM (page-level) methods in standard SQL. SYSTEM is faster but results can be biased toward rows that happen to share a page. Oracle uses non-standard SAMPLE syntax.

Standard Syntax

SELECT * FROM tbl TABLESAMPLE BERNOULLI(10); -- ~10% of rows, row-level SELECT * FROM tbl TABLESAMPLE SYSTEM(10); -- ~10% of rows, page-level (faster)

Version Support

MySQL: Not supported PostgreSQL: Since 9.5 SQL Server: Since 2005 Oracle: Since 8i SQLite: Not supported

Per-Database Syntax & Notes

MySQL

Not supported. ORDER BY RAND() LIMIT n works but scans the full table and is very slow on large datasets.

SELECT * FROM tbl ORDER BY RAND() LIMIT 1000; -- slow SELECT * FROM tbl WHERE id % 10 = 0 LIMIT 1000; -- faster but deterministic/biased

PostgreSQL

Two methods: BERNOULLI (true row-level statistical sample — accurate but slower) and SYSTEM (page-level — faster but may have clustering bias). Optional REPEATABLE(seed) for reproducible results. Custom sampling methods can be added via extensions (e.g., tsm_system_rows).

SELECT * FROM tbl TABLESAMPLE BERNOULLI(10); SELECT * FROM tbl TABLESAMPLE SYSTEM(10); SELECT * FROM tbl TABLESAMPLE BERNOULLI(5) REPEATABLE(42); -- reproducible -- With extension: SELECT * FROM tbl TABLESAMPLE SYSTEM_ROWS(1000); -- exact row count

SQL Server

TABLESAMPLE SYSTEM(n PERCENT) or TABLESAMPLE (n ROWS). Only the SYSTEM method (page-level). Optional REPEATABLE(seed).

SELECT * FROM tbl TABLESAMPLE SYSTEM(10 PERCENT); SELECT * FROM tbl TABLESAMPLE (1000 ROWS) REPEATABLE(42);

Oracle

Non-standard SAMPLE clause syntax (not called TABLESAMPLE). SAMPLE(pct) is row-level (like BERNOULLI); SAMPLE BLOCK(pct) is page-level (like SYSTEM). Optional SEED(n) for reproducibility.

SELECT * FROM tbl SAMPLE(10); -- row-level ~10% SELECT * FROM tbl SAMPLE BLOCK(10); -- page-level ~10% SELECT * FROM tbl SAMPLE(10) SEED(42);

SQLite

Not supported. Workaround: random rowid selection.

SELECT * FROM tbl WHERE rowid IN ( SELECT rowid FROM tbl ORDER BY RANDOM() LIMIT 1000 );