MySQL LN() Function – Easily find the natural logarithm of a number in MySQL

By /
LN Function

In this tutorial, we will learn about the MySQL LN() function. While studying maths, you must have come across the operation – loge (logarithm to the base of the mathematical constant e). Finding the loge of a number is also known as finding the natural logarithm of a number.

Logarithm, in general, is a widely used operation across many fields, and therefore, MySQL provides us with the LN() function.

The LN() function in MySQL is a mathematical function which is used to find the natural logarithm of a number. The LN() function is the inverse of the EXP() function. Let us explore the LN() function now.

Syntax of MySQL LN() function

LN(number);Code language: SQL (Structured Query Language) (sql)

Where ‘number’ is a number greater than 0 whose natural logarithm is to be found out.

Examples of MySQL LN() function

Let us start with some basic examples. How about finding the values of loge 2, loge 10 and loge 32 using the LN() function. The queries for these operations are –

SELECT LN(2); 
SELECT LN(10); 
SELECT LN(32);Code language: SQL (Structured Query Language) (sql)

And we get the output as,

Ln Basic Example

MySQL LN() With Zero and NULL

I mentioned earlier that the ‘number’ argument passed to the LN() function should be greater than 0. So what if you pass zero or NULL to the LN() function? In such a case, the LN() function will return NULL. Let us demonstrate this using the below queries.

SELECT LN(0); 
SELECT LN(NULL);Code language: SQL (Structured Query Language) (sql)

And the output is,

MySQL LN Zero Null

MySQL LN() With Negative Numbers

As you saw in the previous example, LN() returns NULL if the argument is 0 or NULL. MySQL LN() shows the same behavior when we pass a negative number as its parameter. Let us see this behavior using the below examples.

SELECT LN(-5); 
SELECT LN(-2);Code language: SQL (Structured Query Language) (sql)

And we get the output as,

MySQL LN Negative

LN() With Expressions

Let us now use expressions with MySQL LN(). Consider the below query.

SELECT LN(3+3), LN(6);Code language: SQL (Structured Query Language) (sql)

Both expressions will output the same result as shown below.

MySQL LN Expression 1

Let us now find the result of the following expression using the LN() function – loge 23. The query is,

SELECT LN(POW(2, 3));Code language: SQL (Structured Query Language) (sql)

And the output is,

MySQL LN Expression 2

How are LN() and EXP() related?

LN() is the inverse of the EXP() function. Let us see this using the below example. How about finding the natural logarithm of e? We do so using the below query.

SELECT LN(EXP(1)) AS 'ln(e)';Code language: SQL (Structured Query Language) (sql)

And the output is,

MySQL LN E 1

As you can see, the output is 1, which is the number we passed as the argument. This shows that LN() is the inverse of EXP(). Let’s see a couple more examples before we move on to the next topic. Consider the following expressions – loge e2 and loge e55. The queries are –

SELECT LN(EXP(2)); 
SELECT LN(EXP(55));Code language: SQL (Structured Query Language) (sql)

And the output is,

Ln E 2

Using LN() With Tables

Consider the below Numbers table.

Ln Numbers Table

Simple Example

Let’s kick things off with a simple example by finding the natural logarithm of all values in the X column. The query is,

SELECT LN(X) FROM Numbers;Code language: SQL (Structured Query Language) (sql)

And we get the output as,

Ln Table Example 1

LN() With the UPDATE statement

With reference to the previous example, let us create a new column named LN_Of_X in the ‘Numbers’ table which contains the natural logarithm values of the values in the X column. The queries for this are –

ALTER TABLE Numbers ADD LN_Of_X float; 
UPDATE Numbers SET LN_Of_X=LN(X); 
SELECT * FROM Numbers;Code language: SQL (Structured Query Language) (sql)

We use the ALTER statement to add the LN_Of_X column of data type float to the Numbers table. Next, we update the pre-populated NULL values of the LN_Of_X column with the natural logarithm values of the X column using the UPDATE statement. Lastly, we display the newly updated Numbers table using the SELECT statement.

The output is –

MySQL LN Table Example 2

Conclusion

Finding the natural logarithm of a number is one of the most important and widely used mathematical operations. I would encourage you to try the various use cases of LN() in MySQL for practice.

References