SQL ABS: Absolute Value, Deltas, and Tolerance Checks

ABS returns the absolute value of a number — its magnitude with the sign stripped off. It sounds trivial, but this is the function that answers "how far apart" whenever direction does not matter: the gap between expected and actual totals, a salary difference, a deviation from a target.

Magnitude and absolute value

ABS(x) drops the sign: a negative turns positive, while zero and positives stay as they are.

SELECT ABS(-7),     -- 7
       ABS(7),      -- 7
       ABS(0);      -- 0

The result type matches the argument type: an integer stays integer, a numeric stays numeric, and a double precision stays float. That is handy when you want to avoid implicit casts and precision loss in monetary columns.

-- magnitude of a signed balance adjustment
SELECT id, amount, ABS(amount) AS magnitude
FROM orders
WHERE status = 'refund';

Here refund rows store amount as a negative, but the report wants the size of the refund, not the direction of the cash flow.

Delta and distance between values

The most common use is the distance between two numbers: ABS(a - b). Argument order stops mattering, because the absolute value is symmetric.

-- how far each order amount sits from the user's average
SELECT o.id,
       o.amount,
       ABS(o.amount - avg_amount) AS delta
FROM orders o
JOIN (
    SELECT user_id, AVG(amount) AS avg_amount
    FROM orders
    GROUP BY user_id
) a ON a.user_id = o.user_id;

The same idea applies to salaries: how far an employee's pay sits from the department average, regardless of whether it is higher or lower.

SELECT name, dept, salary,
       ABS(salary - AVG(salary) OVER (PARTITION BY dept)) AS gap
FROM employees
ORDER BY gap DESC;

ABS(a - b) is a one-dimensional Euclidean distance. It underpins discrepancy reports, reconciliations, and "nearest record" lookups over a numeric field.

Tolerance checks in WHERE

Comparing float values for exact equality is risky: accumulated rounding error will almost always make a = b false. The right move is to check that the difference falls within a small tolerance (epsilon).

-- find orders whose amount is within 0.01 of a target
SELECT id, amount
FROM orders
WHERE ABS(amount - 100.00) <= 0.01;

The same pattern reconciles two totals that should match in theory but drift by a cent in practice because of rounding.

SELECT o.id
FROM orders o
JOIN ledger l ON l.order_id = o.id
WHERE ABS(o.amount - l.posted_amount) > 0.005;   -- flag real mismatches

• Gotcha: the predicate ABS(amount - 100) <= 0.01 cannot use an ordinary index on amount, because the column is wrapped in a function. For a range, amount BETWEEN 99.99 AND 100.01 is faster — it is a sargable predicate that maps onto a B-tree index.
• On integers ABS loses no precision, but watch for overflow: ABS of the minimum int4 value lands outside the type range and raises an error.

Pairing with SIGN

ABS answers "how much", and SIGN answers "which way". Together they fully describe a deviation: magnitude plus direction.

-- magnitude and direction of deviation from a target salary
SELECT name,
       salary,
       ABS(salary - 50000)  AS gap,
       SIGN(salary - 50000) AS direction   -- -1 below, 0 equal, 1 above
FROM employees;

SIGN returns -1, 0, or 1, so the original value can always be rebuilt: value = SIGN(value) * ABS(value). That is convenient when you sort first by the size of the deviation and then by its sign.

Engine differences are minor, but a few are worth knowing:

• In PostgreSQL and MySQL the name is just ABS. In ClickHouse the function is spelled abs (lowercase, like every function), and the result on signed types stays signed.
• In ClickHouse, abs of an unsigned integer returns the same unsigned value — there are no negatives there to flip.
• ABS(NULL) always yields NULL. If the tolerance is computed over a column that can hold NULL, wrap it in COALESCE, or the row silently drops out of the check.

Bottom line: ABS is "distance without direction". Reach for it on deltas and reconciliations, add SIGN when you need direction, and rewrite predicates as BETWEEN when speed and indexing matter.