Understanding the SQRT() Function in SQLite

by

In SQLite, the SQRT() function calculates the square root of a number. It returns the value which, when multiplied by itself, equals the input number.

Note that SQLite returns NULL if you try to calculate the square root of a negative number, NULL, or a value of the wrong type.

Syntax

SQRT(x)

Where x is a non-negative numeric expression.

Example 1

Here’s a simple example to start:

SELECT SQRT(16);

Output:

+----------+
| SQRT(16) |
+----------+
| 4.0      |
+----------+

Example 2

Passing a value of:

  • 0 results in 0
  • 1 results in 1
  • A negative value results in null
  • null results in null
  • The wrong argument type results in null

Here’s an example to demonstrate:

SELECT 
       SQRT(0),
       SQRT(1),
       SQRT(-16),
       SQRT(null),
       SQRT('two');

Output:

+---------+---------+-----------+------------+-------------+
| SQRT(0) | SQRT(1) | SQRT(-16) | SQRT(null) | SQRT('two') |
+---------+---------+-----------+------------+-------------+
| 0.0     | 1.0     | null      | null       | null        |
+---------+---------+-----------+------------+-------------+

Example 3

Here’s an example showing SQRT() with various numbers:

-- Create a table with test values
CREATE TABLE numbers (
    num_id INTEGER PRIMARY KEY,
    value FLOAT
);

-- Insert test values
INSERT INTO numbers (value) VALUES 
    (0),    -- sqrt = 0
    (1),    -- sqrt = 1
    (4),    -- sqrt = 2
    (9),    -- sqrt = 3
    (16),   -- sqrt = 4
    (25),   -- sqrt = 5
    (100),  -- sqrt = 10
    (-4);   -- sqrt = NULL (cannot take square root of negative number)

-- Calculate square roots
SELECT 
    value,
    ROUND(SQRT(value), 6) as square_root,
    -- Verify the result by squaring it
    ROUND(SQRT(value) * SQRT(value), 6) as verification
FROM numbers;

Output:

+-------+-------------+--------------+
| value | square_root | verification |
+-------+-------------+--------------+
| 0.0   | 0.0         | 0.0          |
| 1.0   | 1.0         | 1.0          |
| 4.0   | 2.0         | 4.0          |
| 9.0   | 3.0         | 9.0          |
| 16.0  | 4.0         | 16.0         |
| 25.0  | 5.0         | 25.0         |
| 100.0 | 10.0        | 100.0        |
| -4.0  | null        | null         |
+-------+-------------+--------------+

Here, the verification column verifies the result, in that it multiplies the result of SQRT(value) with SQRT(value). We can see that it results in the original value.

Common Use Cases of SQRT()

The SQRT() function is frequently used in:

  • Geometric calculations (e.g., finding distances using the Pythagorean theorem)
  • Statistical computations (e.g., calculating standard deviations)
  • Scientific formulas
  • Data normalization
  • Engineering calculations