Back to Library

POWER() in SQL

Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.

Syntax

POWER(base, exponent) or POW(base, exponent)
Return type
NUMERIC/FLOAT/DOUBLE (depends on DBMS and input types)

POWER() Function Example

SELECT POWER(2, 3); 
-- Output: 8 (2 raised to power 3)

What is POWER() in SQL?

The POWER() function in SQL returns the result of raising a base number to a specified exponent. It is used for mathematical computations, scientific calculations, and financial modeling, where exponential growth or scaling is required. In some databases like MySQL, the function is also written as POW(), but both perform the same operation. The POWER() function is widely supported in SQL Server, MySQL, PostgreSQL, and Oracle, making it a useful tool for calculations involving exponents, interest rates, and data transformations.

Parameters:

  • base: The base number to be raised to a power.

  • exponent: The power to raise the base number to.

Example Use Cases:

-- Calculate compound interest

SELECT principal * POWER(1 + rate, years) AS amount FROM investments; 
-- Output:
amount
-------
11592.74
8236.89
15783.62
9456.31
12847.95

-- Compute area of squares 

SELECT POWER(side_length, 2) AS area FROM squares; 
-- Output:
area
----
25
64
100
144
81

-- Calculate volume of cubes 

SELECT POWER(edge_length, 3) AS volume FROM cubes;
-- Output:
volume
------
27
64
125
216
1000

Notes:

  • Behavior: Returns NULL if base is negative and exponent is fractional

  • Performance Considerations: More computationally intensive than basic arithmetic

  • Version Info: Core mathematical function available in all major DBMS

  • Deprecated/Recommended Alternatives: Some DBMS use POW() as alternative syntax

Error Handling:

  • Error: Returns NULL if input is NULL

  • Recommendation: Use COALESCE if NULL handling needed

Supported Databases:

DBMS
Function / Syntax
Example
Behavior with NULL
MySQL

POWER() or POW()

POWER(2, 3)

Returns NULL

PostgreSQL

POWER()

POWER(2, 3)

Returns NULL

SQL Server

POWER()

POWER(2, 3)

Returns NULL

SQLite

POWER()

POWER(2, 3)

Returns NULL

BigQuery

POWER()

POWER(2, 3)

Returns NULL

Snowflake

POWER()

POWER(2, 3)

Returns NULL

Athena

POWER()

POWER(2, 3)

Returns NULL

Related Functions:

SIGN()

Returns the sign of a number: -1 for negative numbers, 0 for zero, and 1 for positive numbers.

Returns the sign of a number: -1 for negative numbers, 0 for zero, and 1 for positive numbers.

Returns the sign of a number: -1 for negative numbers, 0 for zero, and 1 for positive numbers.

Returns the sign of a number: -1 for negative numbers, 0 for zero, and 1 for positive numbers.

ABS()

Returns the absolute (positive) value of a number by removing the negative sign if present.

Returns the absolute (positive) value of a number by removing the negative sign if present.

Returns the absolute (positive) value of a number by removing the negative sign if present.

Returns the absolute (positive) value of a number by removing the negative sign if present.

RAND()/RANDOM()

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

MOD()

Returns the remainder after dividing one number by another. Also available as % operator in many databases.

Returns the remainder after dividing one number by another. Also available as % operator in many databases.

Returns the remainder after dividing one number by another. Also available as % operator in many databases.

Returns the remainder after dividing one number by another. Also available as % operator in many databases.

POWER()

Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.

Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.

Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.

Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.

FLOOR()

Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.

Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.

Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.

Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.

CEIL()/CEILING()

Rounds a number up to the nearest integer or to a specified decimal place. Always rounds away from zero for positive numbers and toward zero for negative numbers.

Rounds a number up to the nearest integer or to a specified decimal place. Always rounds away from zero for positive numbers and toward zero for negative numbers.

Rounds a number up to the nearest integer or to a specified decimal place. Always rounds away from zero for positive numbers and toward zero for negative numbers.

Rounds a number up to the nearest integer or to a specified decimal place. Always rounds away from zero for positive numbers and toward zero for negative numbers.

ROUND()

Rounds a number to a specified number of decimal places or to the nearest integer.

Rounds a number to a specified number of decimal places or to the nearest integer.

Rounds a number to a specified number of decimal places or to the nearest integer.

Rounds a number to a specified number of decimal places or to the nearest integer.

One place for all your queries,
directly on your SQL editor