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);Code language: SQL (Structured Query Language) (sql)
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);Code language: SQL (Structured Query Language) (sql)
And the output is,
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);Code language: SQL (Structured Query Language) (sql)
And we get the output as,
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);Code language: SQL (Structured Query Language) (sql)
And the output is,
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);Code language: SQL (Structured Query Language) (sql)
And the output is as follows,
MySQL EXP() With Values in a Table
Let us look at a few examples of
EXP() with tables. Consider the below ‘Numbers’ table.
We will try to find values of expressions in which e is raised to the values in the X column.
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;Code language: SQL (Structured Query Language) (sql)
And we get the output as follows,
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;Code language: SQL (Structured Query Language) (sql)
First, the EXP(-X) part gets executed and then that result is subtracted from one. We get the output as follows.
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;Code language: SQL (Structured Query Language) (sql)
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,
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.