SQL POWER: Raises a Number to a Power of a Specified Number

The SQL POWER function returns the numeric_expression raised to the power of a specific number.

Syntax

The following illustrates the syntax of the POWER function.

POWER(numeric_expression,power)Code language: SQL (Structured Query Language) (sql)

Arguments

The POWER function accepts 2 arguments:

numeric_expression
is an expression that evaluates to a number.

power

is the power to which to raise numeric_expression. The power can be a number or an expression that evaluates to a number.

Return type

The POWER expression returns a number whose data type is the type the first argument i.e. the result of the numeric_expression.

Examples

The following statement returns the first argument because any number raised to the power of 1 equals the number itself.

SELECT POWER(10,1);Code language: SQL (Structured Query Language) (sql)

 power
-------
    10
(1 row)Code language: SQL (Structured Query Language) (sql)

The following example returns 1 because any number raised to the power of 0 becomes 1.

SELECT POWER(10,0);
Code language: SQL (Structured Query Language) (sql)

 power
-------
     1
(1 row)Code language: SQL (Structured Query Language) (sql)

The following example returns the square of a number.

SELECT POWER(10,2);
Code language: SQL (Structured Query Language) (sql)

 power
-------
   100
(1 row)Code language: SQL (Structured Query Language) (sql)

The following statement returns the cube of a number.

SELECT POWER(10,3);Code language: SQL (Structured Query Language) (sql)

 power
-------
  1000
(1 row)Code language: SQL (Structured Query Language) (sql)

If the power is a fractional value, the POWER function return the root of the corresponding value e.g. 1/2 is a square root, 1/3 is a cubic root, etc.

SELECT POWER(100,0.5);
Code language: SQL (Structured Query Language) (sql)

       power
--------------------
 10.00
(1 row)Code language: SQL (Structured Query Language) (sql)

If the power is a negative number, the POWER function returns a value that equals the reciprocal of the number raised to the opposite positive power. See the following example.

SELECT POWER(100,-1);
Code language: SQL (Structured Query Language) (sql)

 power
-------
  0.01
(1 row)Code language: SQL (Structured Query Language) (sql)

Notes

In some database systems such as Microsoft SQL server, the POWER function returns zero (0) if the second argument is a negative number.

Besides the POWER function, MySQL and PostgresQL accept the POW function as a synonym for POWER function.

Was this tutorial helpful ?