On NULL Values

Simply, NULL values are meaningless expressions. You can not do any comparison on NULL values. Be careful when dealing NULL values. I demonstrate a simple example on Oracle to show how NULL values effect on queries program blocks.

NULL değerler hiçbirşey ifade etmeyen ifadelerdir. 1 ve 0 oalarak da karşılığı belirlenemezler. O nedenle yazılan kodlarda ve sorgularda bu duruma dikkat etmek gerekir. Oracle ile yaptığım örnekte bu durumu göstermeye çalıştım:

create table n(i number, n varchar2(5));
BEGIN
INSERT INTO n VALUES (1, NULL);
INSERT INTO n VALUES (2, '');
INSERT INTO n VALUES (3, 's');
END;

commit;

SELECT * FROM n

SELECT COUNT(*) FROM n WHERE n.n IS NULL
--2
SELECT COUNT(*) FROM n WHERE n.n = NULL
--0
SELECT COUNT(*) FROM n WHERE n.n = ''
--0

Another example is a program block:

DECLARE
a VARCHAR2(5) := NULL;
b VARCHAR2(5) := NULL;
BEGIN
IF a IS NULL
THEN
dbms_output.put_line('a is NULL');
END IF;

IF b IS NULL
THEN
dbms_output.put_line('b is NULL');
END IF;

IF a = b
THEN
dbms_output.put_line('a = b is TRUE');
ELSE
dbms_output.put_line('a = b is FALSE');
END IF;

IF a != b
THEN
dbms_output.put_line('a != b is TRUE');
ELSE
dbms_output.put_line('a != b is FALSE');
END IF;

END;

Output is

a is NULL
b is NULL
a = b is FALSE
a != b is FALSE



Another is:

SELECT COUNT(*) FROM co
--34474
SELECT COUNT(*) FROM co WHERE ma = 'X'
--29617
SELECT COUNT(*) FROM co WHERE ma <> 'X'
--0
SELECT COUNT(*) FROM co WHERE ma IS NULL
--4857




Be careful!