SIGN in SQL: the Sign of a Number and Branching by Direction

SIGN returns the sign of a number: -1 for negatives, 0 for zero, and 1 for positives. It is a tiny function, but it turns "how much" into "which way" and collapses piles of clunky CASE logic into one call.

What SIGN returns

The signature is trivial: a number in, one of three values -1, 0, 1 out.

SELECT SIGN(-42)   AS neg,   -- -1
       SIGN(0)     AS zero,  --  0
       SIGN(17.5)  AS pos;   --  1

The result type mirrors the argument: numeric in gives numeric out, double precision gives double precision. If the argument is NULL, the result is NULL too -- not a fourth state, just ordinary null propagation.

A practical use is normalizing order amounts down to the direction of the cash flow, ignoring magnitude:

SELECT id,
       amount,
       SIGN(amount) AS direction  -- -1 refund, 0 zero, 1 charge
FROM orders;

Branching on the direction of change

Most often you reach for SIGN to classify the gap between two values as "up", "down", or "flat". Compare an order amount with the same user's previous order via a window function:

SELECT id,
       user_id,
       amount,
       SIGN(amount - LAG(amount) OVER (
           PARTITION BY user_id ORDER BY created_at
       )) AS trend  -- -1 down, 0 flat, 1 up
FROM orders;

The three values unfold cleanly into labels with CASE. Note that the WHEN 0 branch matches exact equality, not "roughly equal":

SELECT id,
       CASE SIGN(amount - 100)
           WHEN -1 THEN 'below target'
           WHEN  0 THEN 'on target'
           WHEN  1 THEN 'above target'
       END AS bucket
FROM orders;

SIGN with ABS: direction apart from magnitude

Any number factors as x = SIGN(x) * ABS(x). Splitting direction from magnitude is the main reason to keep SIGN in your toolbox. For example, total what left as refunds versus what came in as charges in a single pass:

SELECT SUM(CASE WHEN SIGN(amount) = 1 THEN amount ELSE 0 END) AS charged,
       SUM(CASE WHEN SIGN(amount) = -1 THEN ABS(amount) ELSE 0 END) AS refunded
FROM orders;

The same pairing helps when sorting by closeness to a target: order by ABS (how far) while keeping the sign separate to show which side a salary sits relative to a department benchmark:

SELECT name,
       dept,
       salary,
       SIGN(salary - 60000) AS side,   -- which way off
       ABS(salary - 60000)  AS gap     -- how far off
FROM employees
ORDER BY gap DESC;

Numeric stability and gotchas

SIGN itself is stable: it never loses precision and never overflows, because it only ever yields three values. The danger hides in the argument.

• Floating-point zero. In a computation 0.0 may arrive as a tiny 1e-18, and then SIGN returns 1, not 0. If the zero branch matters, round before you sign: SIGN(ROUND(x::numeric, 6)).
• NULL falls through every branch. In CASE SIGN(x) none of -1, 0, 1 will match a NULL -- add an explicit IS NULL check or an ELSE.
• Integer division. SIGN(a / b) over integers divides first: SIGN(3 / 4) is SIGN(0) = 0. Cast to a fractional type before dividing.

Differences between engines are small but worth knowing:

• PostgreSQL returns the same numeric type as the argument.
• MySQL has the same SIGN() name but always returns an integer -1/0/1.
• ClickHouse offers sign() (lowercase) with the same -1/0/1 behavior.

-- Pitfall: integer division hides the real sign
SELECT SIGN(3 / 4)               AS wrong,  -- 0
       SIGN(3.0 / 4)             AS right_; -- 1

Remember the identity x = SIGN(x) * ABS(x) -- it solves half of every "up or down" question without a single nested CASE.