MySQL POW() and POWER() – Easily find the exponent in MySQL

By /
POW And POWER

In this tutorial, we will learn about the MySQL POW() and POWER() functions. Suppose you have to do certain calculations on the data in your table which involves finding the squares, cubes or other powers raised to the number. For such operations, MySQL provides us with the POW() and POWER() functions. The POW() and POWER() functions are used to find out the value of a number raised to the power of another number. Both POW() and POWER() function do the same operation. They even have a similar syntax. The only difference is the name of the function.

Syntax of MySQL POW() and POWER()

POW(base, exponent); 
POWER(base, exponent);Code language: SQL (Structured Query Language) (sql)

Both, the base and exponent arguments are required arguments.

Examples of MySQL POW() and POWER()

Let us look at a few examples. Let us use the POW() or POWER() functions to calculate the result of the following – 23, 152, 1212 and 205. We will use aliases to make our output readable. We use the following queries,

SELECT POW(2, 3) AS CubeOfTwo; 
SELECT POW(15, 2) AS SquareOfFifteen; 
SELECT POWER(12, 12) AS Result; 
SELECT POWER(20, 5) AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is,

MySQL Pow And Power Basic Examples

POW() and POWER() with Zero and NULL Values

You must have learnt about powers and exponents in high school math. Any number raised to the power 0 is equal to 1. Furthermore, zero raised to the power of any number is 0. POW() and POWER() adhere by these rules as demonstrated by the queries and output below:

SELECT POWER(20, 0) AS Result; 
SELECT POWER(0, 2) AS Result;Code language: SQL (Structured Query Language) (sql)
Pow And Power Basic Examples 2 1

POW() and POWER() return NULL as the result if either of the arguments is NULL. This can be demonstrated by the queries and output below:

SELECT POWER(20, NULL) AS Result; 
SELECT POWER(NULL, 4) AS Result;Code language: SQL (Structured Query Language) (sql)
MySQL Pow And Power Basic Examples 2 2

Mathematical Operations with POW() and POWER()

Suppose you have an expression which involves exponents as well as addition and subtraction. We can use mathematical operations along with the POW() and POWER() functions. Let us write queries for the following two expressions: 42 + 32 and 42 – 22.

SELECT POW(4, 2) + POW(3, 2) AS Result; 
SELECT POW(4, 2) - POW(2, 2) AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is,

Pow And Power Basic Examples 3

POW() and POWER() With Tables

Let us now take a look at some practical examples that you may encounter while working with POW() and POWER() in MySQL. Consider the below ‘Square’ table.

Square Table
Square Table

The above table shows us the figure id and the length of the sides of the square. The formula to find the area of a square is (side)2. How about displaying the square’s figure id and corresponding area? We use the SELECT statement for the query.

SELECT FigureId, POW(Side, 2) AS AreaOfSquare FROM Square;Code language: SQL (Structured Query Language) (sql)

And we get the output as,

Pow Power Area Of Square Example

Now, what if I want to create a new column in the ‘Square’ table title AreaOfSquare that contains the area of the corresponding square? We will use the ALTER and UPDATE statements. 

First, we will use the ALTER command to add a column named AreaOfSquare with the float data type. This will add the AreaOfSquare column in the table but it will be populated with NULL values.

Hence, we use the UPDATE statement next and set the value of the column as POW(Side, 2).

Finally, we will use the SELECT statement to see our newly updated table.

ALTER TABLE Square ADD AreaOfSquare float; 
UPDATE Square SET AreaOfSquare = POW(Side, 2); 
SELECT * FROM Square;Code language: SQL (Structured Query Language) (sql)

And we get the output as,

Pow Power Area Of Square Example 2

Conclusion

MySQL POW() and POWER() functions are very useful in many mathematical operations in MySQL. You may use either function, it won’t make a difference. I suggest you try playing around with this function by trying different use cases.

MySQL is a considerably easy, yet highly efficient and powerful in handling database queries. But the most important part is getting used to how these queries work. If you have any questions, feel free to connect with us and we can help you out! Happy learning 🙂

References: https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html