How to fix ERROR 1365: Division by zero in MySQL

MySQLINTERMEDIATEMEDIUMUpdated Dec 25, 2025· 158 views

MySQL ERROR 1365 occurs when a query divides by zero, halting execution. Fix it using NULLIF(), CASE statements, or filtering with WHERE clauses to handle zero denominators safely.

Quick fix

Fixes ERROR 1365: Division by zero

SELECT 
  total_sales,
  revenue / NULLIF(units_sold, 0) AS price_per_unit
FROM sales
WHERE date >= CURDATE();
  1. Use NULLIF() to convert zero denominators to NULL
  2. Use CASE statement for conditional logic
  3. Use IF() function for simple two-branch logic
  4. Filter rows with WHERE clause
mysql — error
SELECT 
  total_sales,
  revenue / NULLIF(units_sold, 0) AS price_per_unit
FROM sales
WHERE date >= CURDATE();
ERROR 1365: Division by zero

What this error means

MySQL Error 1365 (SQLSTATE 22012) is triggered when a SQL expression attempts division by zero, which is mathematically undefined. This error can occur during SELECT, UPDATE, INSERT...SELECT, or stored procedure execution. When MySQL encounters a division by zero, it immediately stops processing the statement and raises this error to prevent invalid calculations. By default, division by zero would return NULL, but depending on your SQL mode settings (particularly ERROR_FOR_DIVISION_BY_ZERO combined with strict mode), MySQL enforces this as an error to ensure data integrity and protect against unexpected NULL values in your results.

Typical symptoms

  • Queries fail with "ERROR 1365: Division by zero" (SQLSTATE 22012)
  • INSERT or UPDATE statements terminate unexpectedly when dividing values
  • Stored procedures or triggers abort when encountering division expressions with zero denominators
  • Aggregate functions like SUM() or COUNT() return 0 instead of expected values, causing downstream divisions to fail
  • LEFT JOINs create NULL values that convert to 0 in arithmetic operations, triggering the error

Common causes

  1. A column used as a divisor contains zero values due to data entry errors or missing data
  2. Aggregate functions like COUNT() or SUM() return 0 when no rows match the filter condition
  3. LEFT JOINs or OUTER JOINs produce NULL values that are implicitly converted to 0 in arithmetic expressions
  4. User input validation failed on the application side, allowing 0 to flow into a calculation query
  5. A subquery or intermediate calculation evaluates to 0 before being used as a divisor
  6. Strict SQL mode (STRICT_ALL_TABLES or TRADITIONAL) with ERROR_FOR_DIVISION_BY_ZERO is enabled, treating division by zero as an error instead of returning NULL

How to fix "ERROR 1365: Division by zero"

1Use NULLIF() to convert zero denominators to NULL

The NULLIF() function returns NULL if the divisor equals zero, preventing the division operation:

sql
SELECT 
  total_sales,
  revenue / NULLIF(units_sold, 0) AS price_per_unit
FROM sales
WHERE date >= CURDATE();

When units_sold is 0, NULLIF() returns NULL, making the division result NULL instead of an error. This is the most concise solution for most use cases.

2Use CASE statement for conditional logic

Add explicit conditional logic to handle the zero case:

sql
SELECT 
  total_sales,
  CASE 
    WHEN units_sold = 0 THEN NULL
    ELSE revenue / units_sold
  END AS price_per_unit
FROM sales;

CASE provides more control—you can return NULL, 0, a default value, or even log the condition. Use this when you need custom behavior beyond just returning NULL.

3Use IF() function for simple two-branch logic

The IF() function provides a concise alternative to CASE for simple conditions:

sql
SELECT 
  total_sales,
  IF(units_sold = 0, NULL, revenue / units_sold) AS price_per_unit
FROM sales;

IF() is equivalent to the CASE statement but more compact. It returns NULL when units_sold is 0, otherwise performs the division.

4Filter rows with WHERE clause

Exclude rows where the divisor is zero before performing calculations:

sql
SELECT 
  total_sales,
  revenue / units_sold AS price_per_unit
FROM sales
WHERE units_sold > 0;

This approach works when you only need results for valid calculations. However, it loses rows where the divisor is zero, which may not always be acceptable.

5Add CHECK constraints to prevent zero values in the database

For columns that should never be zero, add a CHECK constraint when defining the table:

sql
CREATE TABLE sales (
  id INT PRIMARY KEY,
  units_sold INT NOT NULL CHECK (units_sold > 0),
  revenue DECIMAL(10, 2) NOT NULL
);

Or on an existing table:

sql
ALTER TABLE sales
ADD CONSTRAINT check_units_positive CHECK (units_sold > 0);

This enforces data integrity at the database level, preventing problematic data from being inserted in the first place.

6Verify your SQL mode settings

Check your current SQL mode to understand when division by zero is treated as an error:

sql
SELECT @@sql_mode;

If ERROR_FOR_DIVISION_BY_ZERO is enabled (typically as part of STRICT_ALL_TABLES or TRADITIONAL mode), division by zero will error during INSERT/UPDATE/CREATE TABLE operations. To temporarily disable strict mode:

sql
SET sql_mode='';

Or remove just the error flag:

sql
SET sql_mode='STRICT_TRANS_TABLES';

Note: Changing sql_mode at runtime only affects the current session. To make permanent changes, edit your MySQL configuration file (my.cnf or my.ini) and restart the server.

Advanced notes

Virtual/Generated Columns: MySQL 5.7 and early versions of 8.0 have a known bug where division by zero in generated columns can cause data consistency issues. If you encounter this, the workaround is to drop the generated columns, fix the underlying data to remove zero divisors, and then recreate the columns. SQL Mode Details: The ERROR_FOR_DIVISION_BY_ZERO flag only affects DML operations (INSERT, UPDATE, CREATE TABLE). SELECT statements always return NULL for division by zero, regardless of SQL mode. If you need stricter behavior in application code, validate divisors before executing queries. Performance Note: NULLIF() and CASE are index-friendly and have minimal performance impact. However, if checking many rows, adding a WHERE clause or CHECK constraint prevents the overhead entirely by preventing invalid data from being stored.

Frequently asked questions

What does "ERROR 1365: Division by zero" mean?
MySQL Error 1365 (SQLSTATE 22012) is triggered when a SQL expression attempts division by zero, which is mathematically undefined. SELECT, or stored procedure execution. When MySQL encounters a division by zero, it immediately stops processing the statement and raises this error to prevent invalid calculations.
How do I fix "ERROR 1365: Division by zero"?
MySQL ERROR 1365 occurs when a query divides by zero, halting execution. Fix it using NULLIF(), CASE statements, or filtering with WHERE clauses to handle zero denominators safely.
What causes "ERROR 1365: Division by zero"?
A column used as a divisor contains zero values due to data entry errors or missing data Aggregate functions like COUNT() or SUM() return 0 when no rows match the filter condition LEFT JOINs or OUTER JOINs produce NULL values that are implicitly converted to 0 in arithmetic expressions
Is the "ERROR 1365: Division by zero" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading