LOG()
SQL · Math Functions
Theory
A logarithm answers the question 'what power must I raise the base to, to get this number?'. With base 10, LOG(1000) is 3 because 10^3 = 1000, and LOG(100) is 2. Logarithms turn multiplication into addition, which is why they compress huge ranges: values from 10 to 10,00,000 become 1 to 6.
Be careful, because the meaning of LOG differs by database. In PostgreSQL and SQLite, LOG(x) is base 10. In SQL Server and MySQL, LOG(x) is the natural logarithm (base e), and you use LOG10(x) for base 10. Oracle and Snowflake require the base: LOG(10, x). Several databases accept a two-argument form, though the argument order varies; in PostgreSQL, MySQL and SQLite it is LOG(base, x), while SQL Server writes LOG(x, base). LOG is undefined for zero and negative numbers. In analytics, logs are used for order-of-magnitude buckets (FLOOR(LOG10(revenue))), for charts of skewed data and for growth calculations.
Why we use it
Business data is often highly skewed: a few customers spend crores while most spend hundreds. Logs shrink that range so values can be compared, bucketed by order of magnitude, or used in models.
When to use it
Use LOG10 when you want 'number of digits' style buckets or a log scale. Use LN for growth rates and statistical formulas. Always state the base explicitly (LOG10, or LOG(10, x)) so the query means the same on every database.
How it works
- Input number, e.g. 1000
- Is the input zero or negative? Yes: Return NULL or an error. No: Continue.
- Is LOG(x) base 10 here? Yes: Yes: PostgreSQL, SQLite. No: No: SQL Server, MySQL — use LOG10.
- Find the power of the base that gives the input
- Return 3
Syntax
LOG(number)Example
SELECT LOG(1000);
-- 3Key points
- Some databases let you pass a custom base: LOG(base, number)
- Undefined for zero or negative input
- LOG(x) is base 10 in PostgreSQL/SQLite but natural log in SQL Server/MySQL
- Write LOG10(x) or LOG(10, x) to make the base explicit
- Oracle and Snowflake require the base argument