RAND() Function in SQL
Introduction
RAND is a database-specific numeric function that generates a pseudo-random value. In MySQL and SQL Server, the common no-argument form generally returns a value greater than or equal to zero and less than one. A later evaluation can return a different value.
SELECT RAND() AS random_value;
-- Example result: 0.6384721
Pseudo-random values support simulations, synthetic test data, randomized ordering, approximate sampling, and simple game-like examples. They do not automatically provide uniqueness, statistical suitability, cryptographic security, or identical evaluation behavior across databases. Correct use begins by defining the required range, distribution, repeatability, sample size, and security level.
Syntax and Database Differences
RAND is not universal SQL syntax. MySQL and SQL Server provide RAND, PostgreSQL commonly provides RANDOM, and Oracle commonly exposes DBMS_RANDOM.VALUE.
| Database | Common form | Random ordering |
|---|---|---|
| MySQL | RAND() | ORDER BY RAND() |
| SQL Server | RAND() | Different techniques are commonly used |
| PostgreSQL | RANDOM() | ORDER BY RANDOM() |
| Oracle | DBMS_RANDOM.VALUE | DBMS-specific expression |
Function names are only the first portability issue. Seeding, per-row evaluation, return types, statement scope, sessions, parallel execution, and sampling features also differ.
Pseudo-Random Rather Than Truly Random
Database generators usually use deterministic algorithms with internal state. Their output appears unpredictable for ordinary application work, but the sequence is produced by a repeatable process. That is why the correct term is pseudo-random.
Ordinary RAND-style functions may be suitable for demonstrations, low-risk simulations, and test-data variation. They should not be assumed suitable for passwords, authentication tokens, encryption keys, reset links, session secrets, lotteries, regulated drawings, or adversarial security decisions.
The Common Zero-to-One Range
A common range is zero inclusive to one exclusive.
0 <= RAND() < 1
Possible values include 0.00042, 0.12567, and 0.92745. One is normally excluded, which enables scaling formulas based on half-open intervals. Verify the exact contract of the target database rather than assuming every random facility uses this range.
Scaling a Random Decimal
Multiplication expands the range. RAND() times 10 gives a decimal from zero up to but not including 10; multiplying by 100 gives a decimal below 100.
SELECT RAND() * 10 AS below_ten,
RAND() * 100 AS below_one_hundred;
Multiple function calls in one statement may or may not behave as independent draws depending on the DBMS and expression context. Test evaluation semantics before building a simulation from several calls.
Random Integers from Zero
For a uniform source in [0, 1), multiplying by a positive count and applying FLOOR produces integers from zero through count minus one.
SELECT FLOOR(RAND() * 10) AS random_digit;
-- Possible values: 0 through 9
FLOOR is easier to reason about than CEIL because the excluded upper boundary maps naturally to the desired highest integer.
Inclusive Integer Ranges
To generate an integer from minimum through maximum inclusive, compute the count of possible integers, multiply RAND by that count, apply FLOOR, and add the minimum.
FLOOR(RAND() * (maximum - minimum + 1)) + minimum
For 20 through 30, there are 11 possible values.
SELECT FLOOR(RAND() * 11) + 20 AS random_value;
Range Validation
The inclusive formula assumes integer bounds and maximum greater than or equal to minimum. Validate both before evaluation. The expression maximum - minimum + 1 can overflow a narrow integer type when bounds are extreme.
Cast to a wide type before subtraction if large ranges are allowed. Reject an empty or reversed interval rather than returning a surprising value.
Off-by-One Errors
FLOOR(RAND() * 10) produces 0 through 9, not 1 through 10. Adding one shifts the range.
SELECT FLOOR(RAND() * 10) + 1 AS one_to_ten;
The number of inclusive integer outcomes is maximum minus minimum plus one. Omitting that final one silently excludes the maximum.
Dice, Coins, and Boolean-Like Values
SELECT FLOOR(RAND() * 6) + 1 AS dice_roll;
SELECT CASE FLOOR(RAND() * 2)
WHEN 0 THEN 'Heads'
ELSE 'Tails'
END AS coin_flip;
These are demonstrations of range mapping, not evidence that the generator is suitable for gambling or regulated outcomes.
Random Percentages
RAND times 100 creates a decimal percentage from zero up to but not including 100. A whole-number percentage from zero through 100 needs 101 possible integers.
SELECT RAND() * 100 AS decimal_percent,
FLOOR(RAND() * 101) AS whole_percent;
Rounding RAND times 100 is not the same distribution as choosing uniformly among 101 whole integers because interior rounded values cover wider source intervals than endpoints.
Random Decimals with Fixed Display Precision
SELECT ROUND(RAND() * 100, 2) AS displayed_value;
ROUND limits the displayed numeric precision but can change the distribution at boundaries. If every cent value must be equally likely, generate an integer number of cents first and divide by 100 using exact decimal arithmetic.
SELECT (FLOOR(RAND() * 10001)) / 100.0
AS uniform_cent_value;
Random Synthetic Prices
For a test price from 10.00 through 100.00 inclusive in cent increments, generate one of 9,001 integer cent offsets.
SELECT (1000 + FLOOR(RAND() * 9001)) / 100.0
AS test_price;
Use DECIMAL for money and label the result synthetic. Random test data should never be mistaken for actual financial data.
Random Categories
SELECT CASE FLOOR(RAND() * 3)
WHEN 0 THEN 'Low'
WHEN 1 THEN 'Medium'
WHEN 2 THEN 'High'
END AS test_priority;
This gives equal intervals to three categories under a uniform generator and appropriate per-evaluation behavior. Real category data often has unequal probabilities, correlations, and business constraints that this example does not model.
Weighted Categories
Unequal probabilities can map cumulative intervals. The following conceptual expression assigns 50 percent Low, 30 percent Medium, and 20 percent High.
WITH draw AS (
SELECT RAND() AS random_value
)
SELECT CASE
WHEN random_value < 0.50 THEN 'Low'
WHEN random_value < 0.80 THEN 'Medium'
ELSE 'High'
END AS test_priority
FROM draw;
Calculate the draw once so every CASE branch uses the same value. Validate cumulative thresholds, require the final boundary to cover the remaining interval, and test observed frequencies over many draws.
Why Calculate the Draw Once
Repeating RAND in multiple parts of one logical decision can produce different values in systems that reevaluate the function. A row might be classified with one draw and displayed with another.
Use a CTE, derived table, lateral expression, or DBMS-appropriate construct to name one draw. Confirm with the execution plan and target DBMS because an optimizer can inline expressions and nondeterministic-function rules differ.
Seeded Randomness
Some systems accept a seed, such as RAND(seed). A seed initializes or influences pseudo-random state and can support reproducible tests, demonstrations, and simulations.
SELECT RAND(100) AS seeded_value;
Seed semantics vary substantially. Repeating the same seed on every row can repeat the same output rather than produce a sequence. Session state, connection pooling, call order, and parallel execution can change reproducibility.
Reproducible Tests
A repeatable test needs more than a numeric seed. Record database product and version, seed, query, row ordering, dataset version, connection behavior, and number of draws. Without stable input order, the same sequence can attach to different rows.
For durable deterministic test assignment, hashing a stable key with a fixed algorithm is often easier to reproduce than relying on session-scoped generator state.
SQL Server Evaluation Semantics
SQL Server RAND can return one value for many rows within a statement rather than a fresh independent value per row. NEWID-based ordering and other techniques are commonly used for row-level variation, but they solve different problems and have their own costs.
Never assume a function written in a SELECT list is evaluated independently for each row. Run a controlled test on the exact database version and query shape.
Random Ordering
MySQL can randomize row order with ORDER BY RAND, while PostgreSQL commonly uses ORDER BY RANDOM.
-- MySQL example for a modest table
SELECT question_id, question_text
FROM questions
ORDER BY RAND()
LIMIT 5;
This is convenient for small question banks and development tools. A random sort provides an exact requested row count when enough rows qualify, but it can be expensive.
Why Random Sorting Is Expensive
The database may scan qualifying rows, generate a random sort key for each row, sort the set, and only then return a small limit. CPU, memory, temporary storage, and latency can grow with the candidate count.
Measure the actual plan and runtime. A query that is acceptable for 1,000 rows may be unsuitable for tens of millions or a high-traffic endpoint.
Approximate Bernoulli-Style Filtering
SELECT *
FROM event_data
WHERE RAND() < 0.10;
Where evaluation is independent per row, this resembles a ten-percent selection probability. It does not guarantee exactly ten percent or an exact row count. From 1,000 rows, results may vary around 100.
DBMS evaluation semantics can invalidate the per-row assumption, so verify before using this pattern.
Exact Sample Size Versus Sampling Probability
ORDER BY a random key plus LIMIT requests an exact number of rows but may require a full sort. A probability predicate avoids sorting but returns a variable count. These are different requirements.
Define whether the application needs exactly n rows, approximately p percent, repeatable membership, equal row probability, grouped sampling, or streaming efficiency before choosing a technique.
DBMS Sampling Features
Some databases offer TABLESAMPLE or other physical sampling features. Such features may sample pages or blocks rather than independent rows, and they can be faster while producing clustered or approximate results.
Read product documentation, test statistical properties on representative storage layouts, and do not assume that a percentage parameter means an exact percentage of rows.
Scalable Random Row Selection
Alternatives to random sorting include sampling a maintained random key, selecting from a precomputed pool, using DBMS sampling, or choosing a random position in a stable eligible set. Each has tradeoffs involving gaps, bias, concurrency, and maintenance.
Choosing a random integer between minimum and maximum ID and then selecting the next row can bias results when IDs have gaps. Analyze the actual key distribution rather than treating convenience as uniformity.
Sampling with Replacement
Independent draws can select the same row more than once. That is sampling with replacement. ORDER BY a random key and LIMIT within one result normally returns distinct table rows, which is sampling without replacement.
Simulations and quiz systems must decide whether repetition is allowed across a session, across users, or across days. Persist selection history when no-repeat behavior is required; RAND alone does not remember prior choices.
Randomness Does Not Guarantee Uniqueness
Random values can collide. Generating FLOOR(RAND() * 100) for many rows inevitably repeats values as the number of draws grows.
Do not use ordinary RAND output as a primary key. Use identity, sequence, UUID/GUID, or another identifier mechanism selected for the database and system architecture. Even UUID design requires understanding version, indexing, and collision assumptions.
Security-Sensitive Randomness
Passwords, tokens, secrets, reset links, encryption keys, and session identifiers require a cryptographically secure random-number generator. Ordinary database RAND functions are not automatically unpredictable to an attacker.
Use a platform or database facility explicitly documented as cryptographically secure and follow the security architecture for encoding, length, storage, rotation, and expiration. Seeding an ordinary generator does not make it secure.
Privacy and Synthetic Data
Randomizing a few columns does not automatically anonymize a production dataset. Combinations of age, location, dates, and rare attributes can still identify people. Synthetic data must satisfy privacy, legal, and statistical requirements.
Prefer dedicated data-generation and masking processes. Preserve referential integrity and valid distributions without copying sensitive values into test environments.
Correlated Synthetic Data
Generating every column independently can create impossible records, such as incompatible dates, totals, categories, or regional values. Realistic test data needs constraints and correlations.
Generate foundational attributes first, then derive dependent values using documented rules. Validate ranges, foreign keys, checks, and aggregate reconciliation after generation.
Transactions, Retries, and Random Results
A retry can generate a different random value, making an operation non-idempotent. If a random choice affects a business decision, persist the chosen value or stable assignment key before side effects occur.
Transaction rollback and generator state can interact differently by DBMS. Test retry behavior, connection pooling, failover, and replicas when reproducibility matters.
Concurrency and Fairness
Independent random selection does not guarantee balanced assignment over short periods. Concurrent workers can select the same unclaimed row unless ownership is acquired transactionally.
Use locking, atomic status changes, queues, or another coordination design. RAND supplies a candidate ordering; it does not provide mutual exclusion, fairness, or exactly-once processing.
Randomized A/B Assignment
Experiment membership usually needs to remain stable for one subject. Calling RAND on each request can move a user between groups and invalidate analysis.
Use a stable hash of subject identifier, experiment identifier, and version, then map the result to buckets. Keep allocation thresholds and eligibility rules versioned. Cryptographic requirements depend on whether assignment must resist manipulation.
Distribution Quality
A generator can produce values in the expected range while still showing bias, correlation, or a short period. Simple database functions are not automatically appropriate for scientific simulation.
For ordinary range mapping, test frequencies across buckets over many draws. For serious simulation, use a reviewed generator and statistical tests appropriate to the model. Passing one frequency check does not prove high-quality randomness.
Modulo Bias
When a fixed-width random integer source is reduced with MOD and its possible values are not evenly divisible by the bucket count, some buckets occur slightly more often. Scaling a documented uniform [0,1) value with FLOOR avoids that particular discrete reduction issue, subject to the generator's finite precision.
Security-sensitive unbiased integer generation typically uses rejection sampling in a secure library rather than ad hoc SQL arithmetic.
RAND in Stored Logic and Generated Columns
Nondeterministic functions are often restricted in generated columns, indexed expressions, persisted computed columns, and deterministic routines. A value that changes by evaluation is not a stable stored derivation.
Generate once and store the result when a durable random attribute is required. Record generation method and version if the distribution may be audited later.
Random Test Ages and Quantities
SELECT FLOOR(RAND() * (65 - 18 + 1)) + 18
AS test_age,
FLOOR(RAND() * 100) + 1
AS test_quantity;
Confirm that the DBMS produces independent draws if both columns are expected to vary separately. Add domain validation after generation.
Random Dates
A random date in a bounded range can be generated by choosing an integer day offset and adding it to the start date. Date-difference and date-add syntax varies by DBMS.
-- Conceptual pattern
start_date + random_integer_from_zero_to_day_count
Define inclusive endpoints, time zone, calendar rules, and whether timestamps or dates are required. Avoid generating invalid local times across daylight-saving transitions.
Monitoring Randomized Workflows
Track bucket counts, sample sizes, duplicate rates, rejected ranges, generation latency, random-sort cost, assignment balance, and seed/version metadata. Alerts should detect distribution shifts and unexpected repeated values without assuming every short-run imbalance is a defect.
Operational monitoring complements statistical validation. A statistically plausible query can still fail because of transaction races or changed DBMS evaluation semantics.
Persisting a Random Shuffle
A random ORDER BY produces a new order whenever it is reevaluated. Pagination over that changing order can show duplicates, omit rows, or move an item between pages. If a user must navigate one stable shuffle, generate an ordering key once and persist it with a shuffle or session identifier.
SELECT session_id,
item_id,
random_order_key
FROM shuffled_item
WHERE session_id = :session_id
ORDER BY random_order_key, item_id;
The stable item ID resolves random-key collisions. Define expiration, regeneration, and behavior when eligible items change after the shuffle is created.
Random Quiz Question Sessions
A quiz that promises no repeated questions should select and store the chosen question IDs before presenting the first page. Running ORDER BY RAND independently for every question can repeat items and makes resume behavior inconsistent.
Persist quiz session, selection order, course version, eligibility filters, and seed or method. If questions are added later, decide whether an active session keeps its original set or incorporates new content. RAND selects candidates; the session model enforces continuity.
Stratified Sampling
A simple random sample can underrepresent small but important groups. Stratified sampling selects within defined categories such as region, severity, or product type.
WITH ranked AS (
SELECT record_id,
category,
ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY RAND()
) AS random_rank
FROM eligible_record
)
SELECT record_id, category
FROM ranked
WHERE random_rank <= :rows_per_category;
This conceptual MySQL-style example can still be expensive. Define how many rows each stratum receives, how small strata are handled, and whether final analysis requires sampling weights.
Weighted Random Selection
Weighted choice gives items different selection probabilities. A correct implementation requires positive weights, a cumulative distribution, one draw over total weight, and a deterministic rule for locating the interval containing that draw.
Do not duplicate rows to approximate weights when values are large or dynamic. That approach wastes resources and can introduce rounding errors. Prefer a reviewed DBMS-specific cumulative-weight query or perform the selection in a tested application library. Validate that total weight is positive and that NULL or negative weights are rejected.
Reservoir Sampling for Streams
Reservoir sampling selects a fixed-size sample from a stream without knowing the final row count and without sorting the complete dataset. It is an algorithm rather than one portable SQL RAND expression.
For very large or streaming data, use a database feature, processing engine, or library designed for reservoir sampling. Confirm whether the source order itself is biased and whether distributed partitions are merged with correct weights.
Deterministic Hash Sampling
A stable hash threshold can create repeatable membership without generator state. Hash a stable key with a fixed algorithm, normalize it to a known range, and include rows below a threshold.
This supports repeatable train/test splits and recurring audits, but hash syntax and output differ by database. Record the algorithm, seed or salt, normalization rule, and threshold. A hash sample is deterministic, not secret, unless designed with an appropriate keyed cryptographic method.
Randomized Test Failures
Random test data can expose edge cases, but an unrecorded draw makes a failure difficult to reproduce. Log the seed, generated inputs, generator version, and scenario identifier whenever a randomized test fails.
Use constrained generators that produce valid data most of the time and targeted invalid data under explicit cases. Purely arbitrary values often spend test effort on malformed combinations while missing important domain boundaries.
Fairness and Statistical Audits
When random assignment affects users, rewards, or opportunities, review more than overall bucket counts. Analyze outcomes across relevant groups, eligibility rules, time periods, devices, regions, and retry paths. Operational exclusions can create bias even when the random generator is uniform.
Define acceptable deviations before reviewing results and involve statistical, legal, and domain experts where decisions are consequential. RAND provides numbers; fairness depends on the entire selection process.
Randomness on Replicas and Distributed Systems
Nondeterministic statements can complicate statement-based replication, replay, and distributed execution. Different nodes may evaluate a random function independently and produce different rows or values unless the chosen result is logged or replicated as data.
Review replication mode, failover, retry, and read-replica behavior. Generate and persist a decision on the authoritative writer when consistency matters, rather than expecting separate nodes to recreate the same draw.
Property-Based Range Verification
Generate many values and assert that every result stays within its declared range. For inclusive integers minimum through maximum, assert integer form, lower bound, upper bound, and that every expected outcome appears over a sufficiently large nonsecurity test run.
Frequency checks should use statistical acceptance bands, not exact equality. A fair generator can produce uneven short runs. Conversely, seeing every outcome once does not prove uniformity. Keep functional boundary tests separate from statistical-quality tests.
Testing Strategy
Test lower and upper boundaries, inclusive integer formulas, reversed ranges, one-value ranges, large bounds, decimal cents, seeded repeatability, per-row evaluation, multiple calls, transaction retries, random ordering, sample counts, and DBMS syntax.
Statistical tests should use many draws and tolerant acceptance bands rather than expecting exact frequencies. Functional tests should inject or control randomness where possible so failures are reproducible.
Common Mistakes
Frequent mistakes include off-by-one range formulas, expecting uniqueness, using RAND for secrets, assuming a fresh value per row, believing a probability filter returns an exact count, and running random sorts on huge tables.
Other mistakes include reseeding every row, confusing displayed rounding with equal discrete outcomes, using random IDs as primary keys, ignoring correlations in synthetic data, and failing to persist business-critical random choices.
Best-Practice Checklist
- Define range, distribution, count, repeatability, and security requirements first.
- Verify the random function and evaluation semantics of the target DBMS.
- Use FLOOR with the correct inclusive-range count for integers.
- Validate bounds and protect intermediate range arithmetic from overflow.
- Calculate one draw once when one decision must use one value.
- Use seeds only with documented DBMS semantics.
- Do not assume random values are unique or cryptographically secure.
- Distinguish exact-size samples from probability samples.
- Measure random-sort plans on realistic data volumes.
- Persist random business decisions that must survive retries.
- Use stable hashing for durable experiment assignment.
- Test distributions, boundaries, concurrency, and portability.
Interview Questions and Answers
What does RAND return? In MySQL and SQL Server, it commonly returns a pseudo-random value from zero inclusive to one exclusive.
How do you generate 1 through 10? Use FLOOR(RAND() * 10) + 1 under the common range contract.
How do you generate minimum through maximum? Use FLOOR(RAND() * (maximum - minimum + 1)) + minimum after validating bounds.
Does RAND guarantee uniqueness? No. Random values can repeat.
Is RAND secure? Ordinary RAND should not be assumed cryptographically secure.
Why can ORDER BY RAND be slow? It may generate keys and sort all qualifying rows before returning a small subset.
Does RAND() < 0.10 return exactly ten percent? No. Under appropriate evaluation it represents an approximate per-row selection probability.
Why use a seed? A supported seed can help reproducibility, but semantics are DBMS-specific.
What does PostgreSQL commonly use? RANDOM rather than RAND.
What is the SQL Server per-row concern? RAND may produce one value shared across rows in a statement, depending on query context.
Key Takeaway
RAND-style functions generate pseudo-random numeric values for testing, simulation, sampling, and random selection. Their simplicity hides important differences in range mapping, evaluation scope, reproducibility, distribution, performance, and security.
RELIABLE RANDOM WORKFLOW = DEFINED DISTRIBUTION AND RANGE
+ VERIFIED DBMS SEMANTICS
+ CORRECT BOUNDARY MAPPING
+ EXPLICIT REPRODUCIBILITY POLICY
+ SCALABLE SELECTION STRATEGY
+ SECURITY-APPROPRIATE GENERATOR
Use ordinary pseudo-random SQL functions only for suitable low-risk purposes, persist choices that must survive retries, and test statistical as well as operational behavior. Random-looking output alone does not prove fairness, uniqueness, security, or correctness.