-
Junior Member
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
-
Expert Member
Re: Concept of inserting NULL
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
-
Junior Member
Re: Concept of inserting NULL
As count() ignores null values it showing this kind of result.
-
Re: Concept of inserting NULL
That is simply because everytime you insert ,it creates a new row in the table. and count() counts those rows.
-
Junior Member
Re: Concept of inserting NULL
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules