Plus a value null in oracle

Question

In orther language(C#, Sql Server) i see: 1 + null, or '1' + null is null, But in Oracle result is: 1 || null = 1 but '1' + null = null. example:

select 1 || null from dual ;
select '1' + null from dual;

Result: 1, null

Who can tell me why?

"But in Oracle result is: 1 || null is null " No it isn't – APC Oct 10 '17 at 06:37 — APC, Oct 10 '17 at 06:37

Thilo · Accepted Answer · 2017-10-10T06:29:04.980

4

Oracle treats NULL and the empty string as the same thing.

So 1 || NULL is the same as 1 || "" (which is '1' -- this is string concatenation in SQL, not a logical OR).

For + both arguments are converted to numbers, and any arithmetic operation involving NULL yields NULL.

edited Oct 10 '17 at 06:29

answered Oct 10 '17 at 06:23

Thilo

1

But why: select case when null = '' then 1 else 0 end from dual; is 0? if null and "" is the same it will 1? – A Good Boy Oct 10 '17 at 06:35
1

@NguoiTotbung - for the same reason `null = null` is false: NULL is **never** equal to anything. That's why SQL language has the `is null` operator (SQL Server has this too). – APC Oct 10 '17 at 07:00

1 Answers1