Arithmetic Operators

Arithmetic result types and division-by-zero behaviour vary by database and data type. In MySQL, / is division and DIV is integer division.

1. Introduction

Arithmetic operators in SQL are used to perform mathematical calculations.

They are simple, but extremely useful.

In beginner SQL, they look basic.

In real-world SQL, they become powerful because they allow you to calculate values directly inside queries.

This means SQL can do math while reading data.

You do not always need to fetch raw data and calculate in your application code.

SQL can calculate it for you.

This is heavily used in:

• reports
• dashboards
• analytics
• finance
• inventory
• KPIs

2. What Are Arithmetic Operators?

Arithmetic operators are symbols used to perform mathematical operations.

They work with:

• numbers
• numeric columns
• expressions
• computed values

SQL supports the same core arithmetic operators you already know from math.

3. List of Arithmetic Operators

These are the standard arithmetic tools in SQL.

4. Basic Arithmetic Examples

Arithmetic operators can be used directly inside SQL.

SQL can behave like a calculator.

Addition

SELECT 10 + 5 AS result;

Output

result

15

Subtraction

SELECT 10 - 5 AS result;

Output

result

5

Multiplication

SELECT 10 * 5 AS result;

Output

result

50

Division

SELECT 10 / 5 AS result;

Output

result

2

Modulus

SELECT 10 % 3 AS remainder;

Output

remainder

1

Explanation

10 % 3 means:

• divide 10 by 3
• quotient = 3
• remainder = 1

SQL returns the remainder.

Integer Division (MySQL Only)

SELECT 7 DIV 2 AS quotient;

Output

quotient

3

Explanation

DIV returns only the whole-number quotient.

It removes decimals.

• 7 / 2 = 3.5
• 7 DIV 2 = 3

This is MySQL-specific.

5. Using Arithmetic Operators on Table Data

Arithmetic becomes much more useful when applied to table columns.

Instead of static numbers, SQL performs math on every row.

This allows SQL to calculate dynamic values while fetching data.

6. Example Table — countries

7. Arithmetic on Columns

Query

SELECT country, population / 2
FROM countries;

Explanation

SQL divides the population by 2 for every row.

It performs the calculation row by row.

Output

Why This Matters

SQL is not just reading stored values.

It is computing new values dynamically.

This is a core idea in SQL analytics.

8. Real Example — Population Density

A common real-world example is calculating population density.

Formula:

density = population / area

This value is not stored in the table.

SQL calculates it on the fly.

Query

SELECT country, population / area AS density
FROM countries;

Explanation

For every row, SQL calculates:

population / area

Then names the result density.

Output

(Actual values depend on numeric precision)

Why This Is Powerful

The density column does not exist in the table.

SQL creates it dynamically during query execution.

This is one of SQL’s biggest strengths.

9. The Problem — Division by Zero

Now consider this query:

SELECT country, population / area AS density
FROM countries;

What happens if one row has:

area = 0

This causes division by zero.

What Happens in MySQL?

MySQL does not crash.

Instead:

• result becomes NULL
• SQL raises a warning

Example

SHOW WARNINGS;

MySQL may show:

Division by 0

Why This Is Dangerous

The query still runs.

That makes this dangerous in production.

Because:

• no crash happens
• result silently becomes NULL
• warning may be ignored
• reports become incorrect

This is a very common production issue.

10. Production-Safe Division

To safely avoid division-by-zero errors, use:

NULLIF()

Safe Query

SELECT country,
       population / NULLIF(area, 0) AS density
FROM countries;

How It Works

NULLIF(area, 0) means:

• if area = 0 → return NULL
• otherwise → return area

So:

• normal rows → division works
• zero rows → becomes population / NULL

And:

anything / NULL = NULL

No warning. No crash. Safe result.

Why This Is Better

This is the correct production-safe pattern.

It prevents:

• division warnings
• broken analytics
• unsafe calculations

This is how professional SQL is written.

11. NULLIF() Is Not Only for Numbers

NULLIF() is a general SQL function.

It is not limited to numeric safety.

It can be used anywhere you want to convert one value into NULL.

12. Example — Empty String Handling

SELECT NULLIF(city, '')
FROM countries;

Explanation

This means:

• if city = '' → return NULL
• otherwise → return city

Useful when cleaning bad or incomplete data.

Why This Matters

In real-world data:

• empty strings often mean missing data
• SQL may treat them differently from NULL

NULLIF() helps normalize dirty data.

13. Common Use Cases of Arithmetic Operators

Arithmetic operators are heavily used in:

• revenue calculations
• tax calculations
• discounts
• profit margins
• stock valuation
• analytics
• KPIs
• percentages
• ratios

They are one of the most useful tools in practical SQL.

14. Common Mistakes to Avoid

1. Forgetting That SQL Can Do Math

Many beginners calculate values in application code.

Often SQL can do it directly and more efficiently.

2. Ignoring Division by Zero

This is one of the most common SQL production mistakes.

Always protect division using:

NULLIF(column, 0)

3. Ignoring Warnings

MySQL warnings are easy to miss.

But ignored warnings often become bad reports.

Always check warnings when testing calculations.

4. Forgetting AS

Without aliases, calculated columns become messy and hard to read.

Always name computed columns clearly.

15. Summary

Arithmetic operators allow SQL to perform math directly inside queries.

They are used for:

• basic math
• computed columns
• dynamic metrics
• analytics

SQL can calculate values row by row while fetching data.

This makes queries more powerful and reduces application-side work.

Final Mental Model

Arithmetic operators allow SQL to answer:

• Can I calculate this now?
• Can SQL compute this per row?
• Can I safely avoid broken math?
• Can I create derived values on the fly?