POWER()
SQL · Math Functions
Theory
POWER(base, exponent) raises a number to a power: POWER(2, 10) is 1024, POWER(10, 3) is 1000, POWER(9, 0.5) is 3 (a square root) and POWER(2, -1) is 0.5. It is the SQL way to write 'base multiplied by itself exponent times'.
Its main business use is growth. Compound interest is principal × (1 + rate)^years, so ₹1,00,000 at 8% for 5 years is ROUND(100000 * POWER(1.08, 5), 2) = ₹1,46,932.81. The same formula drives CAGR, inflation adjustment and exponential decay. POWER exists in PostgreSQL, MySQL (also as POW), SQL Server, Oracle, Snowflake and SQLite builds with math functions; PostgreSQL also has the ^ operator. One gotcha: in SQL Server the result takes the type of the base, so POWER(2, 0.5) returns 1 because 2 is an integer; write POWER(2.0, 0.5) to get 1.414. Large exponents can overflow, so keep values in a sensible range.
Why we use it
Growth compounds: interest, population, prices and user counts grow by a percentage of the previous value, not by a fixed amount. POWER computes such repeated multiplication in a single expression.
When to use it
Use POWER for compound interest, CAGR, squared distances and scoring formulas. For square roots SQRT is clearer. For simple squaring of a column, x * x is fine and avoids type surprises.
How it works
- Base and exponent, e.g. (1.08, 5)
- Is the exponent a fraction? Yes: Compute a root (0.5 = square root). No: Repeated multiplication.
- Does the result overflow? Yes: Error or Infinity — use DECIMAL. No: Continue.
- Return base^exponent
Syntax
POWER(base, exponent)Example
SELECT POWER(2, 10);
-- 1024Key points
- Equivalent to the ** operator in some languages
- Also available as POW() in MySQL
- PostgreSQL also offers the ^ operator: 2 ^ 10
- SQL Server returns the base's type: POWER(2, 0.5) = 1, use POWER(2.0, 0.5)
- A fractional exponent is a root: POWER(x, 0.5) = SQRT(x)