MySQL ABS Function with Simple Examples

This tutorial explains MySQL ABS() which is a Math/Trig function. It takes a number as input and determines its absolute (positive) value. We’ll describe the usage of this method with the help of simple examples.

1. ABS() Syntax
2. ABS() Simple Examples
3. ABS() for Expression
4. ABS() with Tables

Let’s now go through each of the sections one by one.

MySQL ABS() Function

As stated initially, ABS() is a built-in MySQL function that does some math calculations to derive the absolute value of a number.

In Maths terms, an absolute value represents the length of a number on the number line from zero irrespective of the direction from where the 0th number rests. The ABS value can never be negative. For example, the ABS value of (2 + (-7)) is five.

So, let’s now see the details and check out how can we use it.

Syntax

Below is the signature of this method:

ABS(number);

Below are the descriptions of the parameters in the above function.

+----------+-------------------------------------+
| # Param | # Description                        |
+----------+-------------------------------------+
| number   |  A literal number or an expression. |
+----------+-------------------------------------+

If the number is a negative value, then the ABS() method converts it to a positive one. When it is either zero or positive, then ABS() makes no difference.

The return type of the value is similar to the input argument. The below diagram shows the graphical representation of MySQL ABS() function:

Let’s now consider some examples of ABS().

MySQL ABS() Simple Examples

The below example displays the output of calling the ABS() function on three different values:

SELECT 
    ABS(-4), 
    ABS(2), 
    ABS(4);

After running this statement, the output comes as:

+---------+--------+--------+
| ABS(-4) | ABS(2) | ABS(4) |
+---------+--------+--------+
| 4	  | 2	   | 4      |
+---------+--------+--------+

We’ve not applied ABS() on decimal numbers, let’s check that out:

SELECT
    ABS( -13      ),
    ABS( -13.7    ),
    ABS( -13.79   ),
    ABS(  13.79   ),
    ABS(  20 * -1 );

After we run the given statement, it gets us the following output:

13	13.7	13.79	13.79	20

ABS() for Expression

We can determine the absolute value of expr statements, such as the following:

SELECT ABS(8+3);

After running this statement, the output comes as:

+----------+
| ABS(8+3) |
+----------+
|       11 |
+----------+

One more example is:

SELECT ABS(-8+2);

After running this statement, the result comes as:

+-----------+
| ABS(-8+4) |
+-----------+
|         4 |
+-----------+

As you can see, we get a different result than we’d see if we print the expression itself (without calling the ABS()). Let’s check what that would give us:

SELECT -8+4;

The output goes like this:

+------+
| -8+4 |
+------+
|   -4 |
+------+

Also Read: MySQL FIND_IN_SET Function with Simple Examples

Using ABS() with Tables

This function gives you a positive magnitude number whether you pass a positive or negative value. So far, we’ve seen it in action in conjunction with a bare SELECT clause. Now, we’ll see how we can operate it on tables.

CREATE TABLE EMPL
    (
        empl_id int NOT NULL,
        empl_name VARCHAR(40),
        empl_title VARCHAR(20),
        empl_rank float
    );

INSERT INTO EMPL
    (
        empl_id, empl_name, empl_title, empl_rank
    )
VALUES
    (1, 'OLIVIA', 'Designer', -15.98),
    (2, 'AMELIA', 'Programmer', 0.378),
    (3, 'EMILY', 'S/W Tester', 89.764),
    (4, 'ISABELLA', 'N/W Engineer', 51.99);

SELECT * FROM EMPL;

After creating the EMPL table as above, we get the following output of the last SELECT statement.

1 OLIVIA    Designer      -16
2 AMELIA    Programmer    0
3 EMILY	    S/W Tester    90
4 ISABELLA  N/W Engineer  52

Now, let’s apply the ABS function on the emp_rank column and see how it changes the values.

SELECT empl_rank, abs(empl_rank) FROM EMPL;

After running the query with ABS, the output changes as follows:

empl_rank ABS(empl_rank)
-15.98	  15.979999542236328
0.378	  0.3779999911785126
89.764	  89.76399993896484
51.99	  51.9900016784668

We hope that after wrapping up this tutorial, you will feel comfortable using the MySQL ABS() method. However, you may practice more with examples to gain confidence.

Also, to learn SQL from scratch to depth, read our step-by-step MySQL tutorial.