Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT POWER(2, 10) AS kilo, SQRT(144) AS twelve;

Try it

SQL Powers and roots

Press Run (or Ctrl + Enter) to see the result here.

SQL Compound growth and distance

Press Run (or Ctrl + Enter) to see the result here.

SQL Invalid input gives NULL in SQLite

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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
SQL
-- 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.0 or CAST(2 AS FLOAT) when you want decimals.
  • Negative base with a fractional exponent has no real answer; databases return NULL or an error.
  • POWER(0, 0) is 1 in 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.
  • NULL in, NULL out.