A Complete Guide to the MySQL ABS() Function

What ABS() returns

ABS(X) returns the absolute value of X; if X is NULL, the result is NULL. MySQL derives the result type from the argument type, so do not assume every input type is preserved unchanged. See the MySQL 9.7 ABS() reference.

SELECT ABS(-12);               -- 12
SELECT ABS(12);                -- 12
SELECT ABS(NULL);              -- NULL
SELECT ABS(ROUND(-12.345, 2)); -- 12.35

A common use is comparing magnitudes, such as the difference between two measurements:

SELECT target_value,
       measured_value,
       ABS(target_value - measured_value) AS difference
FROM measurements
ORDER BY difference
LIMIT 5;

Signed BIGINT minimum-value edge case

The most negative signed BIGINT value has no positive counterpart in the signed BIGINT range. MySQL therefore returns an error for ABS(-9223372036854775808). If the result must be represented exactly, convert the input to a wider exact type first:

SELECT ABS(CAST('-9223372036854775808' AS DECIMAL(20, 0)));
-- 9223372036854775808

The cast happens before ABS() is evaluated. Choose a DECIMAL precision that can represent the full range of your input values.

NULL and string inputs

ABS(NULL) returns NULL. A string argument is evaluated in a numeric context, so nonnumeric text can be converted with a warning rather than rejected as invalid input. Do not use ABS() as a data-validation function. Validate or explicitly cast incoming text, and inspect warnings when testing conversions. See MySQL’s type-conversion rules.

Filter by absolute value

A predicate such as WHERE ABS(amount) <= 5 applies a function to the indexed column, so a regular index on amount may not be usable for the range condition. For a numeric amount and a nonnegative limit, the equivalent range predicate is:

SELECT id, amount
FROM payments
WHERE amount BETWEEN -5 AND 5;

Check the actual plan with EXPLAIN; index use depends on the schema, predicate, and optimizer estimates.

For the concise syntax and documented return behavior, see the MySQL ABS() reference page. For adjacent math functions, see ROUND(), SIGN(), and FLOOR().