MySQL LOG() – [With Practical Examples]

By /
LOG() Function

In this tutorial, we will learn about the MySQL LOG() function. In an earlier article, we studied the LN() function which finds the natural logarithm of a function. We also studied the LOG2() and LOG10() functions that return the logarithm of a number to the base 2 and 10 respectively.

But won’t it be easy if we had a function that was flexible enough to implement LN() as well as LOG2() and LOG10() like operations?

Furthermore, what if we want to find the result of operations like log6 or log20 and so on? For this purpose, MySQL provides us with the LOG() function.

The LOG() function returns the natural logarithm of a given number if only one argument is provided to it. If two arguments are provided, then it finds the logarithm of the second argument where the base is the first argument.

Syntax of MySQL LOG()

LOG(number)Code language: SQL (Structured Query Language) (sql)

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

OR

LOG(x, number)Code language: SQL (Structured Query Language) (sql)

Where ‘number’ is a number greater than 0 whose logarithm to the base ‘x’ is to be found out. The parameter ‘x’ should be greater than 1.

Examples of MySQL LOG()

Let us kick things off with a basic example. Let us find the natural logarithm of 5, 0.2 and 95 using the MySQL LOG() function. The queries are – 

SELECT LOG(5); 
SELECT LOG(0.2); 
SELECT LOG(95);Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL LOG Basic Example

LOG() With Negative Values

MySQL LOG() returns NULL when we pass a negative number as its parameter. Let us see this behaviour using the below examples.

SELECT LOG(-7); 
SELECT LOG(-0.6);Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL LOG Negative Example

LOG() With Expressions

We can also pass expressions as a parameter to the MySQL LOG() function. Consider the below query – 

SELECT LOG(6*5); 
SELECT LOG(0.8-0.5);Code language: SQL (Structured Query Language) (sql)

And the output is –

Log Expressions

MySQL LOG() With Two Arguments

As we saw in the syntax, we can pass two arguments to the LOG() function where the first argument is the base and the second argument is the number whose logarithm to the base of the first parameter is to be found out. Let us see an example.

Find the result of the following – log4 2, log6 30 and log20 800. The queries are –

SELECT LOG(4, 2); 
SELECT LOG(6, 30); 
SELECT LOG(20, 800);Code language: SQL (Structured Query Language) (sql)

And we get the output as –

Log Two Arguments

Making LOG() Equivalent To LOG2()

LOG2() returns the logarithmic value of a number to the base 2. We can do this operation using the LOG() function by passing 2 as the first argument and a given number as the second argument. Let us see an example. Consider the below queries.

SELECT LOG(2, 4); 
SELECT LOG2(4);

SELECT LOG(2, 52);
SELECT LOG2(52);Code language: SQL (Structured Query Language) (sql)

The queries are to find log2 4 and log2 52 respectively. The output is –

LOG(2, 4)
2
LOG2(4)
2

LOG(2, 52)
5.700439718141093
LOG2(52)
5.700439718141092Code language: SQL (Structured Query Language) (sql)

Making LOG() Equivalent To LOG10()

LOG10() returns the logarithmic value of a number to the base 10. We can do this operation using the LOG() function by passing 10 as the first argument and a given number as the second argument. Let us see an example. Consider the below queries.

SELECT LOG(10, 52); 
SELECT LOG(10, 100); Code language: SQL (Structured Query Language) (sql)

The queries are to find log10 52 and log10 100 respectively. The output is –

LOG(10, 52)
1.716003343634799
LOG10(52)
1.7160033436347992

LOG(10, 100)
2
LOG10(100)
2
Code language: SQL (Structured Query Language) (sql)

MySQL LOG() With NULL Result

If the value of our base parameter is 1 or less than 1, then LOG() returns a NULL result. LOG() also returns NULL if our number is 0 or less than 0. Let us see an example.

SELECT LOG(1, 4); 
SELECT LOG(2, 0);Code language: SQL (Structured Query Language) (sql)

And the output is –

Log Null Result
Log Null Result

LOG() With Tables

Consider the below ‘Numbers’ table.

Log Numbers Table
Numbers Table

The above table already has columns with values populated with the logarithm value of X to the base e, 2 and 10 under the names LN_Of_X, LOG2_Of_X and LOG10_Of_X. Let us compare these values to the values we get from the LOG() function for the same operation.

First, let us write a query that displays the X column and the LN_Of_X column along with the value of the natural logarithm of the values of X found using the LOG() function. The query is –

SELECT X, LN_Of_X, LOG(X) FROM Numbers;Code language: SQL (Structured Query Language) (sql)

And the output is –

Log Table Example1

Next, let us write a query that displays the X column and the LOG2_Of_X column along with the value of the logarithm of the values in the X column to the base 2 found using the LOG() function. The query is –

SELECT X, LOG2_Of_X, LOG(2, X) FROM Numbers;Code language: SQL (Structured Query Language) (sql)

And the output is –

Log Table Example2

Finally, let us write a query that displays the X column and the LOG10_Of_X column along with the value of the logarithm of the values in the X column to the base 10 found using the LOG() function. The query is –

SELECT X, LOG10_Of_X, LOG(10, X) FROM Numbers;Code language: SQL (Structured Query Language) (sql)

And the output is –

Log Table Example3

Conclusion

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

References