SQL POWER Function

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)

Try it

Output:

 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)

Try it

 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)

Try it

 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)

Try it

 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)

Try it

       power
--------------------
 10.000000000000000Code 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. For example:

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

Output:

 power
-------
  0.01Code 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.