Avoid Dividing by Zero in MySQL

You’d like to avoid the “division by zero” error.

For this example , I am using two table one is subjects and second one is marks.

subjects

id	subject_name	subject_marks
1	Math	100
2	English	100

 

marks

marks_id	marks_student_id	marks_subject_id	marks_obtain
1	2	1	40
2	1	2	0

 

We shall perform a division operation of "subject_marks" by "marks_obtain" and display a table containing an additional column named "divided," that shall hold the outcome of the division process.

 

Method 1.

SELECT *, subjects.subject_marks / NULLIF(marks_obtain, 0) AS divided FROM marks LEFT JOIN subjects ON subjects.id=marks_subject_id;

Output:

marks_id	marks_student_id	marks_subject_id	marks_obtain	id	subject_name	subject_marks	divided
1	2	1	40	1	Math	100	2.5
2	1	2	0	2	English	100	NULL

 

Method 2.

SELECT *, CASE WHEN marks_obtain = 0 THEN NULL ELSE subjects.subject_marks / marks_obtain END AS divided FROM marks LEFT JOIN subjects ON subjects.id=marks.marks_subject_id;

Output:

marks_id	marks_student_id	marks_subject_id	marks_obtain	id	subject_name	subject_marks	divided
1	2	1	40	1	Math	100	2.5
2	1	2	0	2	English	100	NULL

The initial method 1 involves utilizing the NULLIF() function, which requires two numerical inputs. In cases where the initial input matches the second input, the function outputs NULL. When marks_obtain equals zero, the divisor becomes NULL; hence, the outcome of the division operation would be NULL.

The alternative approach involves utilizing the CASE statement. Whenever the condition succeeding the WHEN keyword is satisfied (in this particular scenario, the condition implies that marks_obtain is equal to zero), we indicate that the output should be NULL. Conversely, if the condition is not satisfied, the division is carried out normally.