SQL Reference ยท Scalar Functions
SQL ABS() function
Reference for SQL ABS(): the absolute value of a number, differences between values, tolerance comparisons, NULL handling and integer overflow edge cases.
ABS(x) returns the absolute value of a number: its distance from zero, always non-negative. ABS(-7) and ABS(7) are both 7. It is a standard function in every major database.
Its most useful role in queries is measuring a difference without caring about direction, for example "how far is this price from the target?" or "are these two values within a tolerance?".
SELECT ABS(price - 15) AS distance_from_15 FROM books;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
ABS(number)
| Part | Description |
|---|---|
number |
A numeric expression. |
Returns: |number| with the same numeric type as the input. ABS(NULL) is NULL.
Dialect differences
| Database | Notes |
|---|---|
| SQLite | Integer input gives integer, real gives real. ABS of the most negative 64-bit integer raises "integer overflow". Text that is not numeric gives 0.0. |
| PostgreSQL | Defined for all numeric types; fails with "out of range" for the most negative integer of that type. |
| MySQL | Standard; for BIGINT the minimum value overflows with an error. Strings are converted to numbers (a warning for invalid ones). |
| SQL Server | ABS(-2147483648) on an int raises an arithmetic overflow; cast to bigint first. |
| Oracle | Returns NUMBER; no overflow issue for NUMBER but BINARY_DOUBLE follows IEEE rules. |
-- SQL Server SELECT ABS(CAST(-2147483648 AS BIGINT));
Usage notes
- Distance, not direction.
ABS(a - b)equalsABS(b - a). Use it for "within X of" comparisons. - Dates. Subtract dates first, then take
ABS: in SQLite useABS(julianday(a) - julianday(b))for days between two dates. - Sorting by closeness.
ORDER BY ABS(price - target)finds the nearest values; add a tiebreaker such as the id. - Floating-point equality. To compare decimals,
ABS(a - b) < 0.0001is safer thana = b. - Indexes.
WHERE ABS(x) > 5cannot use an index onx. Rewrite asx > 5 OR x < -5. - Sign and magnitude. Use SIGN() for the direction and
ABS()for the size. - Aggregates.
AVG(ABS(error))is the mean absolute error, different fromABS(AVG(error)).