Introduction

When working with numbers in SQL, have you ever wondered whether to use FLOAT, DOUBLE, or DECIMAL?

Especially in systems that handle money or require precise calculations, even a small error can lead to big problems.

In this article, we’ll run actual tests to compare how these three number types behave, and explain the differences in a simple and easy-to-understand way for beginners.

---

Overview of Data Types

Data Type	Characteristics	Precision (Approx.)	Common Use Cases
FLOAT	Lightweight and fast, but not very precise	About 7 digits	Sensor data, statistical data
DOUBLE	Higher precision than FLOAT, widely used	About 15 digits	Scientific computation, charts
DECIMAL	Very high precision, as specified	As defined	Money, tax rates, financials

---

Experiment 1: Insert a simple number and compare the results

Test Code

-- Create table
CREATE TABLE tmp_decimal (
  num_float   FLOAT,
  num_double  DOUBLE,
  num_decimal DECIMAL(20,10)
);

-- Insert data
INSERT INTO tmp_decimal VALUES(
  1111111111.1111111111,
  1111111111.1111111111,
  1111111111.1111111111
);

-- Retrieve data
SELECT * FROM tmp_decimal;

Sample Output

num_float	num_double	num_decimal
1111110000	1111111111.1111112	1111111111.1111111111

Explanation

• FLOAT: Not enough precision, so part of the decimal or even the integer part may be lost or rounded.
• DOUBLE: Retains most of the decimals, but rounding still occurs in the later digits.
• DECIMAL: Stored exactly as specified, with no loss of precision.

As you can see, the same number gives different results depending on the data type used.

---

Experiment 2: Try saving a number with 15 decimal places

CREATE TABLE tmp_precision (
  num_float   FLOAT,
  num_double  DOUBLE,
  num_decimal DECIMAL(30,20)
);

INSERT INTO tmp_precision VALUES (
  0.123456789012345,
  0.123456789012345,
  0.123456789012345
);

SELECT * FROM tmp_precision;

Sample Output:

num_float	num_double	num_decimal
0.123457	0.123456789012345	0.12345678901234500000

---

Experiment 3: Store a very large number with decimals

CREATE TABLE tmp_big_number (
  num_float   FLOAT,
  num_double  DOUBLE,
  num_decimal DECIMAL(38,18)
);

INSERT INTO tmp_big_number VALUES (
  999999999999999.123456789012345678,
  999999999999999.123456789012345678,
  999999999999999.123456789012345678
);

SELECT * FROM tmp_big_number;

Sample Output:

num_float	num_double	num_decimal
1e15	999999999999999.1	999999999999999.123456789012345678

---

When to Use Each Data Type

Use FLOAT / DOUBLE when:

• You’re working with large volumes of data and speed is important
• Minor rounding errors are acceptable (e.g., game coordinates, sensor data, charts)

Use DECIMAL when:

• You need exact precision (e.g., prices, taxes, balances)
• The system requires accurate comparisons and strict equality checks

---

Summary

• FLOAT and DOUBLE are fast, but have precision limitations
• DECIMAL is exact, but slightly heavier in processing and storage
• The key decision point is: “Can I tolerate rounding errors?”

If you’re dealing with money or need accuracy above all, go with DECIMAL.

---

Bonus: Quick Reference Table

Use Case	Recommended Type
Sensor data	FLOAT
Weather temperatures	DOUBLE
Bank account balances	DECIMAL
Product prices with tax	DECIMAL
GPS coordinates	DOUBLE

---