DECIMAL vs FLOAT: How Do They Store Numbers Differently?
Understand how DECIMAL and FLOAT store numbers differently in a database, and when to use each for exact vs approximate data.
Expected Interview Answer
DECIMAL stores numbers as an exact sequence of base-10 digits with a fixed precision and scale, while FLOAT stores numbers as an approximate base-2 (binary) representation, meaning DECIMAL guarantees exact decimal values but FLOAT can introduce tiny rounding errors for common fractions like 0.1.
Internally, DECIMAL keeps each digit exactly as entered, packed into a fixed-width binary-coded format, so arithmetic on DECIMAL values is exact within its declared precision and scale. FLOAT and DOUBLE follow the IEEE 754 standard, representing numbers as a sign, exponent, and mantissa in base 2, which cannot exactly express most base-10 fractions — the same way 1/3 cannot be written exactly in base 10. This makes FLOAT faster and more compact for scientific ranges, but unsuitable anywhere exact decimal arithmetic or equality comparisons are required.
- DECIMAL guarantees exact decimal arithmetic within its scale
- FLOAT/DOUBLE offer wider range and faster computation for approximate math
- Choosing correctly avoids both silent rounding bugs and wasted storage
- Understanding the trade-off prevents comparing floats with equality checks
AI Mentor Explanation
A DECIMAL column is like a scorer writing runs on paper in base-10 digits exactly as they happen — 47 stays 47, no ambiguity. A FLOAT column is more like estimating a bowler's speed from radar in binary-derived increments that round to the nearest fraction the sensor can express, so 142.3 km/h might actually be stored as 142.29999. For an exact stat like a batting average used in official records, scorers use exact fixed-digit arithmetic; for a rough speed-gun display, the small binary approximation goes unnoticed.
Step-by-Step Explanation
Step 1
Understand the storage model
DECIMAL stores exact base-10 digits with fixed precision/scale; FLOAT stores an IEEE 754 binary approximation.
Step 2
Test the exactness requirement
If two independent calculations must always agree to the last digit, that column needs DECIMAL, not FLOAT.
Step 3
Weigh performance and range needs
FLOAT/DOUBLE are faster and handle a wider exponent range, useful for scientific or approximate values.
Step 4
Avoid equality comparisons on FLOAT
Never compare FLOAT columns with = in a WHERE clause; use a tolerance range instead, since exact binary equality is unreliable.
What Interviewer Expects
- Explanation of base-10 exact digits (DECIMAL) vs IEEE 754 binary approximation (FLOAT)
- A concrete example like 0.1 + 0.2 not equaling exactly 0.3 in FLOAT
- Awareness that FLOAT should never be used for currency or equality checks
- Understanding of the storage/performance trade-off in choosing between them
Common Mistakes
- Believing DECIMAL and FLOAT are just different names for the same storage
- Using = to compare two FLOAT values in a query
- Storing money as FLOAT because it "looks close enough"
- Assuming DECIMAL is always slower and therefore avoiding it even for exact use cases
Best Answer (HR Friendly)
“DECIMAL stores numbers as exact decimal digits, so 19.99 is always exactly 19.99, while FLOAT stores an approximate binary version of the number, which can introduce tiny rounding errors. I use DECIMAL anywhere exactness matters, like money, and reserve FLOAT for scientific or approximate values where speed and range matter more than exact precision.”
Code Example
CREATE TABLE PrecisionDemo (
exact_amount DECIMAL(10, 2),
approx_amount DOUBLE PRECISION
);
INSERT INTO PrecisionDemo VALUES (19.99, 19.99);
SELECT exact_amount + 0.01 AS decimal_sum,
approx_amount + 0.01 AS float_sum
FROM PrecisionDemo;
-- decimal_sum is exactly 20.00
-- float_sum may print as 20.000000000000004 depending on the engineFollow-up Questions
- What is the IEEE 754 standard and how does it represent numbers?
- Why should you never use = to compare two FLOAT columns?
- What is the storage cost difference between DECIMAL and DOUBLE?
- How does a database round a DECIMAL value that exceeds its declared scale?
MCQ Practice
1. DECIMAL differs from FLOAT primarily because DECIMAL stores numbers using what base?
DECIMAL stores an exact sequence of base-10 digits, while FLOAT uses an IEEE 754 binary (base-2) approximation.
2. Why is it risky to compare two FLOAT values using = in a query?
Because FLOAT stores an approximate binary value, two values that should be equal can differ by a minuscule rounding amount.
3. Which use case is most appropriate for FLOAT or DOUBLE rather than DECIMAL?
Approximate scientific readings tolerate small binary rounding error and benefit from FLOAT/DOUBLE's speed and range.
Flash Cards
How does DECIMAL store numbers? — As exact base-10 digits with a fixed precision and scale.
How does FLOAT store numbers? — As an IEEE 754 binary approximation using sign, exponent, and mantissa.
Why can 0.1 + 0.2 not equal exactly 0.3 in FLOAT? — Because 0.1 and 0.2 cannot be represented exactly in binary, introducing tiny rounding error.
When should you avoid FLOAT? — Whenever exact decimal arithmetic or equality comparison is required, such as currency.