Dividing By zero

[Mahtab Alam]

I was doing a practice exam question and get across a question that included divide by zero scenario.

I tried this

select 1/0 from dual;

ERROR at line 1:
ORA-01476: divisor is equal to zero (I think for division operation it checks the divisor to make sure it is not a zero)

So why below query does not result in error.

select 1
from dual
where exists( select 1/0
from dual);

1
------
1

[Martin Vashko]

Interesting. I'd say that this is because the EXIST condition just looks whether the subquery returns any row, but doesn't evaluate any expressions in the SELECT clause. It doesn't have to - the existence of rows depends only on the WHERE clause, not on the expressions in the SELECT clause.

This particular case illustrates a general aspect of SQL, which is that you don't have any control over how many times and in which order individual expressions in your SQL statements will be evaluated. This has consequences especially when calling stored procedures from SQL statements, where the number of times they get called may be significant (unlike in pure SQL, where it doesn't matter).

[Mahtab Alam]

I have created a table
create table idm(id number);
insert into idm values(1);
insert into idm values(2);
insert into idm values(3);

update idm
set id=1/0
where id=4;

This does not gives divide by zero error. (this is because here it is not trying to find out 1/0 as there is no row with id=4)
But if I try this

update idm
set id=1/0
where id=3; (this is because here it is trying to solve 1/0 as there is a row with id=3)

this results in divide by zero error