ABS()

Syntax

How it works

ABS() returns the absolute value of a number, meaning its distance from zero regardless of sign. A negative input becomes positive, a positive input is returned unchanged, and zero stays zero. So ABS(-42) and ABS(42) both return 42.

It is part of the SQL standard and behaves the same across MySQL, PostgreSQL, SQL Server and SQLite, so you can rely on it in portable queries. The result keeps the data type of the input: ABS() of an integer is an integer, and ABS() of a decimal keeps its precision and scale.

The most common uses are measuring differences and distances, where the direction does not matter. ABS(a - b) gives the gap between two values as a single positive number, which is handy for tolerance checks such as ABS(a - b) < 0.01. It pairs naturally with ROUND() for display and with MOD() when you need the magnitude of a remainder.

Examples

Absolute difference between two columns

-- How far apart are the forecast and the actual value,
-- regardless of which one is larger
SELECT product,
       ABS(forecast - actual) AS gap
FROM sales
ORDER BY gap DESC;

product | gap
--------+-----
 Widget |  35
 Gadget |  12
 Bolt   |   4

Magnitude of a delta

-- Show the size of each stock change, ignoring
-- whether it was an increase or a decrease
SELECT item,
       change_qty,
       ABS(change_qty) AS magnitude
FROM inventory_moves;

item  | change_qty | magnitude
------+------------+----------
 Nail |        -18 |        18
 Screw|         25 |        25
 Nut  |         -6 |         6

Filter rows within a tolerance

-- Keep only measurements that are within 0.01
-- of the target, in either direction
SELECT sample_id, reading
FROM measurements
WHERE ABS(reading - target) < 0.01;

sample_id | reading
----------+--------
        3 |  5.004
        7 |  4.998

ABS on a negative literal

SELECT ABS(-15)  AS a,
       ABS(15)   AS b,
       ABS(0)    AS c;

a  | b  | c
---+----+---
15 | 15 | 0

ABS with a decimal keeps its scale

SELECT ABS(-3.14159) AS pi_magnitude;

pi_magnitude
------------
     3.14159

Common mistakes

-- Expecting 0 for a missing value, but ABS of
-- NULL is NULL, so the row is filtered out
SELECT id
FROM readings
WHERE ABS(reading - target) < 0.01;  -- NULL reading disappears

-- Handle NULLs explicitly so they are not silently dropped
SELECT id
FROM readings
WHERE reading IS NOT NULL
  AND ABS(reading - target) < 0.01;

-- On a signed INT the most negative value has no
-- positive counterpart, so this can overflow
SELECT ABS(-2147483648);  -- INT range is -2147483648 to 2147483647

-- Widen the type before taking the absolute value
SELECT ABS(CAST(-2147483648 AS BIGINT));

Performance

ABS() is a lightweight scalar function: it inspects one value and flips the sign if needed, so its own cost is negligible compared with reading rows from disk.

The catch is that wrapping a column in ABS() inside a WHERE clause makes the predicate non-sargable, so the database usually cannot use a plain index on that column. A condition like ABS(balance) > 1000 forces a scan that evaluates ABS on every row. If you filter this way often, consider a rewrite such as balance > 1000 OR balance < -1000, or a computed/expression index on ABS(balance) where your engine supports one.

For tolerance joins and range comparisons, remember that ABS(a - b) < t is equivalent to b - t < a AND a < b + t, and the second form can sometimes use an index on a.

Interview questions

SELECT * FROM t
WHERE ABS(a - b) < 0.01;