In this tutorial, we will study the MySQL LOG2()
and MySQL LOG10()
functions. In a previous article, we studied about the MySQL LN()
function which finds the natural logarithm (base e) of a number.
While studying maths, you must have also come across finding the logarithm to the base 2 or 10 of a number (log2 and log10).
Since these are also widely used logarithmic operations, MySQL provides us with the MySQL LOG2()
and LOG10
functions.
- The
LOG2()
function is used to return the base-2 logarithm of a given number. - The
LOG10()
function is used to return the base-10 logarithm of a given number.
Syntax of MySQL LOG2()
LOG2(number);
Code language: SQL (Structured Query Language) (sql)
Where ‘number’ is a numerical value whose base-2 logarithm is to be found out.
Syntax of MySQL LOG10()
LOG10(number);
Code language: SQL (Structured Query Language) (sql)
Where ‘number’ is a numerical value whose base-10 logarithm is to be found out.
Examples of MySQL LOG2()
Let us start by looking at a couple of basic examples. Let us find the values of log2 10 and log2 3. The query for it is:
SELECT LOG2(10);
SELECT LOG2(3);
Code language: SQL (Structured Query Language) (sql)
And the output is –
![MySQL LOG2 Basic Example](https://mysqlcode.com/wp-content/uploads/2021/02/log2-basic-example.png)
MySQL LOG2() With Negative Values
If we pass negative values to the MySQL LOG2()
function, it returns NULL. Let’s see this behavior using the below example.
SELECT LOG2(-4.2);
SELECT LOG2(-15);
Code language: SQL (Structured Query Language) (sql)
And we get the output as,
![MySQL LOG2 Negative Value](https://mysqlcode.com/wp-content/uploads/2021/02/log2-negative-value.png)
MySQL LOG2() With Zero
If we pass zero to the LOG2()
function, it returns NULL. Let us see this using the below example.
SELECT LOG2(0);
Code language: SQL (Structured Query Language) (sql)
And we get the output as –
![Log2 Zero](https://mysqlcode.com/wp-content/uploads/2021/02/log2-zero.png)
LOG2() With Expressions
Let us write a query for the following expression using the LOG2()
function – 1 + 3 log2 2.
SELECT 1+3*LOG2(2);
Code language: SQL (Structured Query Language) (sql)
And we get the output as follows.
![Log2 Expression](https://mysqlcode.com/wp-content/uploads/2021/02/log2-expression.png)
Examples of MySQL LOG10()
Now let us look at a couple of basic examples of the LOG10()
function. Let us find the values of log10 5 and log10 10. The query for it is –
SELECT LOG10(5);
SELECT LOG10(10);
Code language: SQL (Structured Query Language) (sql)
And the output is –
![MySQL LOG10 Basic Example](https://mysqlcode.com/wp-content/uploads/2021/02/log10-basic-example.png)
MySQL LOG10() With Negative Values
If we pass negative values to the LOG10()
function, it returns NULL. Let’s see this behaviour using the below example.
SELECT LOG10(-4.2);
SELECT LOG10(-36);
Code language: SQL (Structured Query Language) (sql)
And we get the output as,
![MySQL LOG10 Negative Value](https://mysqlcode.com/wp-content/uploads/2021/02/log10-negative-value.png)
MySQL LOG10() With Zero
If we pass zero to the LOG10()
function, it returns NULL. Let us see this using the below example.
SELECT LOG10(0);
Code language: SQL (Structured Query Language) (sql)
And we get the output as –
![Log10 Zero](https://mysqlcode.com/wp-content/uploads/2021/02/log10-zero.png)
LOG10() With Expressions
Let us write a query for the following expression using the LOG10()
function – 5 log10 (5) + 3.
SELECT 5*LOG10(5)+3;
Code language: SQL (Structured Query Language) (sql)
And we get the output as follows.
![Log10 Expression](https://mysqlcode.com/wp-content/uploads/2021/02/log10-expression.png)
Using LOG2() and LOG10() With Tables
Consider the below Numbers table.
![Numbers Table Log2](https://mysqlcode.com/wp-content/uploads/2021/02/Numbers-table-log2.png)
Simple Example
Using the SELECT
Statement, LOG2()
function and aliases, let us display the base-2 logarithm of the values in the X column. The query for it is –
SELECT ID, X, LOG2(X) AS LOG2_OF_X FROM Numbers;
Code language: SQL (Structured Query Language) (sql)
And we get the output as follows –
![Log2 Table Example 1](https://mysqlcode.com/wp-content/uploads/2021/02/log2-table-example-1.png)
Let us do something similar and find and display the base-10 logarithm of the values in the X column. Our query is –
SELECT ID, X, LOG10(X) AS LOG10_OF_X FROM Numbers;
Code language: SQL (Structured Query Language) (sql)
And we get the output as,
![Log10 Table Example 1](https://mysqlcode.com/wp-content/uploads/2021/02/log10-table-example-1.png)
LOG2() and LOG10() with the UPDATE Statement
Let us now add two columns to the Numbers table which store the base-2 logarithm values and the base-10 logarithmic values of the X column.
Let us start by creating a column named LOG2_OF_X
of type float using the ALTER
statement. We will then add the base-2 logarithm values of the X column in it using the UPDATE
statement and finally display the new table. The query is –
ALTER TABLE Numbers ADD LOG2_OF_X float;
UPDATE Numbers SET LOG2_OF_X=LOG2(X);
SELECT * FROM Numbers;
Code language: SQL (Structured Query Language) (sql)
And the output is,
![Log2 Table Example 2](https://mysqlcode.com/wp-content/uploads/2021/02/log2-table-example-2.png)
Next, let us create a column named LOG10_OF_X
of type float using the ALTER
statement. We will then add the base-10 logarithm values of the X column in it using the UPDATE
statement and finally display the new table. The query is –
ALTER TABLE Numbers ADD LOG10_OF_X float;
UPDATE Numbers SET LOG10_OF_X=LOG10(X);
SELECT * FROM Numbers;
Code language: SQL (Structured Query Language) (sql)
And we get the output as –
![Log10 Table Example 2](https://mysqlcode.com/wp-content/uploads/2021/02/log10-table-example-2.png)
Conclusion
Finding the logarithm of a number to the base-2 and base-10 is one of the most important and widely used logarithmic operations.