CODEWITHSUNDEEP
sqlnulldb2
TurnCode
TurnCode

Reputation: 3

Coalesce and Nullif

I was wondering why either 1 or 0 turn out (I am a new to SQL).

SELECT COALESCE(100 / NULLIF(60,0),1) gives 1.

SELECT COALESCE(100 / NULLIF(160,0),1) gives 0.

I know that NULLIF returns NULL when the expressions are equal (but neither ist 0), otherwise it takes the first expression. COALESCE takes the first non-NULL value.

My question is, why these results come out - I guess I am missing sth here (with brackets?). Can somebody help?

Upvotes: 0

Views: 505

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12299

The problem is not related to any function in the question.

Expressions:

Two integer operands

If both operands of an arithmetic operator are integers, the operation is performed in binary and the result is a large integer unless either (or both) operand is a big integer, in which case the result is a big integer. Any remainder of division is lost. The result of an integer arithmetic operation (including negation by means of a unary minus operator) must be within the range of the result type.

100 /  60 == 1
100 / 160 == 0

Upvotes: 1

Related Questions