Thread: Concept of inserting NULL

-- I created one table with one unique constraint
SQL> create table t (a int, constraint u_t unique (a))
Table created.
SQL> insert into t values (1);
1 row created.

n Since, we have unique key placed, it won’t accept duplicate value ..
n and would give me an error if I try to insert the value “1” again

SQL> insert into t values (1);
insert into t values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (APPS.U_T) violated

n Here, I am inserting “null” value multiple time and it unique key is not violating
n Because we know in oracle “Null is not equal to another null” and it wont violate
n The unique key – I agree ..

SQL> insert into t values (null);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.

BUT, if you see below query, I tried to get the no. of records against the group of records.
And you can see, here oracle treating Null same as other nulls as it is showing 4 records.
Why -- ? ? ? ?

SQL> select count(*),a from t group by a;
COUNT(*) A
---------- ----------
1 1
4

If you are using the count aggregate function, grouping by a column that contains null values returns a count of zero for the grouping row, since count does not count null values

As count() ignores null values it showing this kind of result.

That is simply because everytime you insert ,it creates a new row in the table. and count() counts those rows.

count syntax is count(/*)
if we use count() it returns the number of not null values in a group of values
if we use count(*) it returns the count of values in a group of values

just create another table and insert some values as well as null and verify both the cases u can clearly find the difference