MySQL EXP() – How to find the power of the exponential constant e in MySQL?

EXP Function

In this tutorial, we will learn about the MySQL EXP() function. While studying maths, you must have come across the exponential constant e. The exponential constant, given by the symbol e, is an important mathematical constant whose value is approximately 2.718. It is the base of natural logarithms. The exponential constant finds applications in maths and various mathematical equations of sound waves, light waves, and quantum waves in physics.

Infact, e is used so often that even scientific calculators have a button – ex to find the powers of this number. The equivalent of that button in MySQL is the EXP() function. The EXP() function returns e raised to the power of the specified number.


Syntax of MySQL EXP()

EXP(expression);

Where ‘expression’ is an expression or a number that is supposed to be the power of e.


Examples of MySQL EXP()

Let us start by looking at a few basic examples. How about we find the answer of e2 and e155 using the EXP() function? Using the SELECT statement, our query is:

SELECT EXP(2); 
SELECT EXP(155);

And the output is,

Exp Basic Example

MySQL EXP() Value

I mentioned before that the value of e is approximately 2.718. What if you want to display that? We can do so by displaying the result of e1. The power 1 raised to e returns the value of e. Let us show this using the below query.

SELECT EXP(1);

And we get the output as,

Exp Value

MySQL EXP() of Zero

You must be curious as to what would be the value of e0 by now. Any number raised to the power 0 returns 1 as the answer. This applies to e as well. Let us show this using the below query,

SELECT EXP(0);

And the output is,

Exp Of Zero

MySQL EXP() Of A Negative Number

Let us now find the value we get when a negative number is the power of e. How about finding the values of the following expressions – e-2 and e-26? We use the below queries:

SELECT EXP(-2); 
SELECT EXP(-26);

And the output is as follows,

Exp Of Negative Numbers

MySQL EXP() With Values in a Table

Let us look at a few examples of EXP() with tables. Consider the below ‘Numbers’ table.

Numbers Table
Numbers Table

We will try to find values of expressions in which e is raised to the values in the X column.

Example 1

Suppose you have the below expression.

Y = e-X

These are the sort of expressions you will come across while using EXP() in MySQL. Yes, this is a very easy example but let’s give you the gist of its use cases without dwelling too much on maths and physics. So let us write a query that displays the value of Y for every value in the X column. We will use an alias called Y to display the result.

SELECT X, EXP(-X) AS Y FROM Numbers;

And we get the output as follows,

Exp Table Example 1

Example 2

Let’s up the game now. Let us use EXP() to find the value of the below expression.

Y = 1 – e-X

We use the below query for it.

SELECT X, 1-EXP(-X) AS Y FROM Numbers;

First, the EXP(-X) part gets executed and then that result is subtracted from one. We get the output as follows.

Exp Table Example 2

Example 3

Now let us see how we can create a new column called Y1 in the numbers table such that it has the values from the below expression.

Y1 = e2x

The queries for this operation are,

ALTER TABLE Numbers ADD Y1 float; 
UPDATE Numbers SET Y1=EXP(2*X); 
SELECT * FROM Numbers;

First, we use the ALTER statement to add a column called Y1 of data type float to the Numbers table. Next, we use the UPDATE statement to update the pre-populated NULL values in Y1 to the value of the given expression. Finally, we use the SELECT statement to display our newly updated table.

We get the output as follows,

Exp Table Example 3

Conclusion

MySQL EXP() becomes a crucial function while dealing with tables and databases used for core mathematical equations and concepts of physics and chemistry. I would suggest you practice using EXP() with even more complicated expressions.