SQL Reference ยท Scalar Functions
SQL POWER() and SQRT() functions
Reference for SQL POWER() and SQRT(): raise a number to a power, take a square root, invalid input such as negative roots, and dialect differences.
POWER(base, exponent) raises a number to a power. SQRT(x) returns the square root. Both return floating-point numbers and appear in growth calculations (compound interest), geometry (distances) and statistics (standard deviation by hand).
A square root is the same as raising to the power 0.5, and POWER(x, 2) squares a value, so you can often use either function.
SELECT POWER(2, 10) AS kilo, SQRT(144) AS twelve;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
POWER(base, exponent) SQRT(x)
| Name | Description |
|---|---|
base |
The number to raise. |
exponent |
The power. May be negative or fractional. |
x |
The number whose square root you want; must be 0 or greater. |
Returns: a floating-point number (DOUBLE/FLOAT, or numeric for numeric input in PostgreSQL). NULL if any argument is NULL.
Dialect differences
| Database | POWER |
SQRT of a negative number |
|---|---|---|
| SQLite | power(x, y), also pow() in stock builds; needs the math functions option |
NULL |
| PostgreSQL | POWER(x, y) or the operator x ^ y |
error |
| MySQL | POWER(x, y) or POW(x, y) |
NULL |
| SQL Server | POWER(x, y); also SQUARE(x) |
error |
| Oracle | POWER(x, y) |
error |
-- PostgreSQL SELECT 2 ^ 10; -- 1024 -- SQL Server: the result has the type of the first argument SELECT POWER(2, -1); -- 0, because 2 is an integer SELECT POWER(2.0, -1);-- 0.5
Usage notes
- Integer first argument in SQL Server truncates the result. Write
2.0orCAST(2 AS FLOAT)when you want decimals. - Negative base with a fractional exponent has no real answer; databases return
NULLor an error. POWER(0, 0)is1in every major database.- Do not use
^for powers in SQLite, MySQL or SQL Server. In MySQL and SQL Server it means XOR; SQLite has no such operator. See bitwise operators. - Large results overflow to an error or infinity in some databases. Check the range, especially in repeated multiplication.
- Standard deviation by hand:
SQRT(AVG(x * x) - AVG(x) * AVG(x))gives the population version; see statistical aggregates. NULLin,NULLout.
Related
ROUND(),ABS(),MOD(),+ - * / % operators- Lessons: Numeric functions, Calculated columns