SELECT 5 / 2 AS result;

SELECT 5.0 / 2 AS result; -- or CAST(5 AS DECIMAL) / 2

Integer division truncates decimals. Returns 2 instead of 2.5. Cast to DECIMAL for precise division. This is a common source of calculation errors.

Always cast to DECIMAL before division for accurate results

High

Returns 2 instead of 2.5 (integer division)

CREATE TABLE orders (total FLOAT);

CREATE TABLE orders (total DECIMAL(10,2));

FLOAT uses binary representation causing rounding errors. 0.1 + 0.2 = 0.30000000000000004. For money, use DECIMAL(10,2) for exact precision. Financial regulations require exact decimal arithmetic.

Never use FLOAT for money. Use DECIMAL(10,2) columns + CAST in calculations

Critical

FLOAT rounding errors: $0.01 discrepancies cascade across millions of transactions

SELECT * FROM employees WHERE CEIL(salary/1000) = 50;

SELECT * FROM employees WHERE salary BETWEEN 49500 AND 50500;

CEIL(salary/1000) calculates for EVERY row before filtering - no index use. 10M rows: 40s vs 200ms with range query. Solution: Use BETWEEN for range, or add computed column.

Never use math functions in WHERE on indexed columns. Use ranges or computed columns

Critical

Functions in WHERE break indexes → full table scan (100x slower)