TABLESAMPLE
TABLESAMPLE (or a SAMPLE clause) returns a random subset of a table's rows, letting queries run faster on a representative fraction of the data instead of the full table.
Overview
Sampling clauses let a query operate on a random subset of rows rather than the entire table — useful for quick exploratory analysis, testing a query's logic cheaply before running it at full scale, or approximate analytics where perfect precision isn't required. Standard SQL expresses this as TABLESAMPLE (method (percentage)) after a table reference.
Copy code
SELECT * FROM large_events_table TABLESAMPLE BERNOULLI (1); -- ~1% of rows
Common sampling methods include Bernoulli (each row independently included with the given probability — precise but scans the full table) and system/block (samples whole storage blocks/pages — faster but less statistically uniform).
DuckDB specifics
DuckDB implements sampling with its own USING SAMPLE clause, applied right after the FROM clause (after joins, before WHERE and aggregation):
Copy code
SELECT * FROM addresses USING SAMPLE 1%; -- default (system) method
SELECT * FROM addresses USING SAMPLE 1% (bernoulli); -- explicit Bernoulli sampling
SELECT * FROM addresses USING SAMPLE 10 ROWS; -- fixed row count instead of a percentage
DuckDB supports sampling by percentage or by a fixed row count, with selectable methods (bernoulli, system, reservoir), and a REPEATABLE (seed) option for deterministic, reproducible samples across runs. DuckDB's USING SAMPLE syntax is the recommended way to sample in DuckDB; the standard TABLESAMPLE keyword form is also recognized for portability with code written against other engines.
Related terms
Approximate aggregation refers to computing statistics like distinct counts, quantiles, or samples using algorithms that trade a small, bounded amount of accuracy for large gains in speed and memory efficiency.
LIMIT →The LIMIT clause restricts a query's result set to a maximum number of rows, often used with ORDER BY to fetch a top-N subset.
WHERE clause →The WHERE clause filters rows in a SQL query based on a boolean condition, keeping only rows for which the condition evaluates to true.
DELETE statement →The DELETE statement removes rows from a table that match an optional WHERE condition, without dropping the table itself.
OVER clause →The OVER clause turns an aggregate or ranking function into a window function by defining the partition, order, and frame it operates on, without collapsing the result set.
HAVING clause →The HAVING clause filters grouped results after aggregation, letting you keep or discard groups based on conditions over aggregate values like SUM() or COUNT().
FAQS
Bernoulli sampling evaluates each row independently against the sample probability, giving a more statistically uniform sample but requiring a full table scan. System (block-based) sampling instead samples whole storage blocks, which is faster but can be less uniform if data isn't randomly distributed within blocks.
Add a REPEATABLE (seed) clause to the sample, which makes the random sample deterministic across repeated runs with the same seed value, instead of returning a different random subset each time.
