POWER()

Syntax

How it works

POWER(base, exponent) returns the base multiplied by itself exponent times, the same as writing base ^ exponent in mathematics. So POWER(3, 2) is 9 and POWER(2, 8) is 256. In MySQL the shorter name POW() is an exact alias for POWER(); the two are interchangeable.

The exponent does not have to be a whole number. Because a fractional exponent is the same as a root, POWER(x, 0.5) is the square root of x and POWER(x, 1.0/3.0) is the cube root. This makes POWER() a general purpose alternative to SQRT() when you need roots other than the square root.

POWER() shows up anywhere exponential math is needed: compound interest and growth projections, computing areas and volumes, decibel and pH style logarithmic scales, and statistical formulas. Because it returns a floating point data type, treat its result as an approximation and round when you need exact money values. Browse the full SQL functions library for related numeric helpers.

Examples

Square a value

-- side length squared gives the area of a square
SELECT side, POWER(side, 2) AS area
FROM shapes;

side | area
-----+-----
   4 |   16
   7 |   49
  10 |  100

Cube a value

-- edge length cubed gives the volume of a cube
SELECT edge, POWER(edge, 3) AS volume
FROM boxes;

edge | volume
-----+-------
   2 |      8
   3 |     27
   5 |    125

Compound interest formula

-- future value = principal * (1 + rate) ^ years
SELECT
  principal,
  rate,
  years,
  ROUND(principal * POWER(1 + rate, years), 2) AS future_value
FROM investments;

principal | rate | years | future_value
----------+------+-------+-------------
   1000.0 | 0.05 |    10 |      1628.89
   5000.0 | 0.07 |     5 |      7012.76

Fractional exponent as a root

-- POWER(x, 0.5) is the square root of x
SELECT
  value,
  POWER(value, 0.5)       AS square_root,
  POWER(value, 1.0 / 3.0) AS cube_root
FROM measurements;

value | square_root | cube_root
------+-------------+----------
  16  |       4.0   |   2.5198
  27  |    5.1962   |   3.0000
 100  |      10.0   |   4.6416

Common mistakes

-- Expecting an exact integer money value here
SELECT POWER(1.1, 3) AS growth;  -- 1.3310000000000004

-- POWER returns a float, so round for display
SELECT ROUND(POWER(1.1, 3), 4) AS growth;  -- 1.3310

-- Fractional root of a negative base is not a real number
SELECT POWER(-8, 0.5);  -- error or NaN, depending on engine

-- Guard the base, or take the root of the absolute value
SELECT POWER(ABS(-8), 0.5) AS root;  -- 2.8284

-- POW does not exist on every database
SELECT POW(2, 10) AS result;  -- fails on SQL Server

-- POWER is the portable, standard spelling
SELECT POWER(2, 10) AS result;  -- 1024

Performance

POWER() is a lightweight scalar function evaluated once per row, so its own cost is negligible compared with reading rows. The thing to watch is that calling it inside a WHERE clause on a column, such as WHERE POWER(value, 2) > 100, hides the column behind a function and prevents the optimiser from using an index on that column.

When you can, rewrite a filter so the column stays bare, for example WHERE value > 10 instead of WHERE POWER(value, 2) > 100. For repeated exponential calculations over large tables, consider storing the computed value in a generated or materialised column so the power is evaluated once at write time rather than on every read.

Because the result is a floating point number, avoid using it directly as a join key or in an equality comparison. Rounding differences between engines can make two mathematically equal results compare as different. See the data types guide for why floating point equality is unreliable.

Interview questions

SELECT POWER(16, 0.5) AS square_root,
       POWER(27, 1.0 / 3.0) AS cube_root;

SELECT ROUND(principal * POWER(1 + rate, years), 2) AS future_value
FROM investments;