LN()
SQL · Math Functions
Theory
LN returns the natural logarithm, the logarithm with base e (about 2.71828). LN(1) is 0, LN(2.71828) is about 1, and LN(10) is about 2.3026. Its inverse is EXP: EXP(LN(x)) gives x back, which is a handy way to check your understanding.
The natural log is the standard choice for growth and statistics. If revenue grows from ₹1,000 to ₹2,000, LN(2000 / 1000) = 0.6931 is the continuous growth rate, and log growth rates can simply be added across periods. A classic SQL trick uses it to multiply values within a group, which SQL has no PRODUCT aggregate for: EXP(SUM(LN(x))) multiplies all x values (only for positive numbers). LN is available in PostgreSQL, MySQL, Oracle, Snowflake and SQLite builds with math functions. SQL Server has no LN; there, LOG(x) with one argument already means the natural log. Like any logarithm, LN is undefined for zero and negative inputs.
Why we use it
Many natural and financial processes grow continuously, and the natural log turns that multiplicative growth into simple addition. It also lets SQL compute products of values with SUM.
When to use it
Use LN for growth rates, log-transforms in statistical work and the EXP(SUM(LN(x))) product trick. Use LOG10 when people need to read 'orders of magnitude'. Filter out zero and negative values first.
How it works
- Input number, e.g. 2000 / 1000 = 2
- Is the input zero or negative? Yes: Return NULL or an error. No: Continue.
- Find the power of e that gives the input
- Return 0.6931
- Use EXP() to convert back if needed
Syntax
LN(number)Example
SELECT LN(2.718281828);
-- ~1Key points
- Distinct from LOG(), which is typically base 10
- Undefined for zero or negative input
- EXP(LN(x)) = x; EXP is the inverse of LN
- SQL Server has no LN — there LOG(x) is already the natural log
- EXP(SUM(LN(x))) multiplies a group of positive values