SQL Random Samples with USING SAMPLE

USING SAMPLE after a table returns a random subset of its rows. Ask for a count (5 rows) or a share (10 percent), and add a seed when you need the same sample again. It's how you look at a few typical rows of a big table, or estimate an average without reading everything.

select ad_group_id, activity_date, clicks
from daily_performance
using sample 5 rows (reservoir, 42)
order by all

These examples use the browser sample of Marketing Campaigns' daily_performance, which has 4,827 rows. Change the 42 and you get a different five.

Three sampling methods

So name bernoulli whenever you ask for a percentage. With a seed, this returns 486 rows, close to 10% of 4,827:

select count(*) as sampled,
       round(avg(clicks), 2) as avg_clicks
from daily_performance
using sample 10 percent (bernoulli, 42)

Its average of 13.92 clicks is near the full table's 13.76. That gap is what a sample costs you.

Seeds and the TABLESAMPLE spelling

The second value in the parentheses is the seed: (bernoulli, 42). DuckDB also accepts the SQL standard's TABLESAMPLE with REPEATABLE. These two return the same 486 rows:

select count(*) from daily_performance
using sample 10 percent (bernoulli, 42);

select count(*) from daily_performance
tablesample bernoulli(10%) repeatable (42);

Without a seed, every run draws a new sample. A seed repeats a sample of the same data; once the table changes, expect a different sample.

The portable form

Sorting by a random number and keeping the first rows works almost everywhere: order by random() limit 5 in DuckDB, PostgreSQL, and SQLite, ORDER BY RAND() in MySQL, and ORDER BY NEWID() in SQL Server. It sorts the whole table to do it, which a real sample avoids.

What can go wrong

• A percentage on a small table returns all or nothing. With no method named, using sample 10% uses system sampling. On these 4,827 rows, 30 runs returned 0 rows most of the time, and otherwise 731, 2,048, or 2,779: whole chunks, never anything near 483.
• The sample is taken before WHERE. 336 rows have more than 50 clicks, but sampling five rows of the table first and then filtering keeps none of them. Filter in a subquery and sample that.
• PostgreSQL's syntax fails. tablesample bernoulli (10) is a percentage in PostgreSQL and a row count in DuckDB, which Bernoulli can't take: Sample method Bernoulli cannot be used with a discrete sample count. Write bernoulli (10%).

select count(*) as kept
from daily_performance
where clicks > 50
using sample 5 rows (reservoir, 42)

select count(*) as kept
from (
  select *
  from daily_performance
  where clicks > 50
) as busy_days
using sample 5 rows (reservoir, 42)

The first returns 0 and the second returns 5.

select count(*)
from daily_performance
tablesample bernoulli (10) repeatable (42)

How other databases differ