Skip to content
Tutorials Center
My learning

Topics

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?".

SQL
SELECT ABS(price - 15) AS distance_from_15
FROM books;

Try it

SQL Absolute value of literals ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Books closest to a target price ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Tolerance check: within one year ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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
-- SQL Server
SELECT ABS(CAST(-2147483648 AS BIGINT));

Usage notes

  • Distance, not direction. ABS(a - b) equals ABS(b - a). Use it for "within X of" comparisons.
  • Dates. Subtract dates first, then take ABS: in SQLite use ABS(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.0001 is safer than a = b.
  • Indexes. WHERE ABS(x) > 5 cannot use an index on x. Rewrite as x > 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 from ABS(AVG(error)).