5 ms·

> The answer to that question is "I don't know -- there is data missing", so the database returns Null. There is no way I'd give that answer to my boss (via da

by chime 17y ago

> The answer to that question is "I don't know -- there is data missing", so the database returns Null. There is no way I'd give that answer to my boss (via dashboard reports or in person) Even though he asked me what the Sales in August were, my answer would have to be $123456 + Missing Sales Staff (which has 12-month rolling of $789) so estimated Sales in August is $123456 + $789. Null does not work in real-world and a computer that says "I don't know" is not a useful computer. I'm definitely in the minority here but personally I think databases would be a lot easier if Null=empty string/0/0D/False or any init value set for a field. I've been coding for 15 years now and have not once used Null in a positive, useful way. The only time I care about Nulls is in WHERE (field='' OR field IS NULL). I would rather it treat nulls like empty strings etc. because then I only have to check for field=''. Nulls in every real-world app I've used mean the same as empty string/false etc. Customer name isn't entered in the system yet? That's not allowed so system will not create the record. Has customer been set as type 'Active'? We don't know? Then they're not active. Do they have multiple shipping addresses? 0 rows in the customer-ship table? That means no, they don't have multiple shipping addresses as of right now. The worst is having a null in a boolean field. That's not boolean, that's a ternary! I either want a TRUE or a FALSE in a boolean field. Null doesn't make sense. Is employee salaried or not? Null is not allowed in that field. The user-interface will not accept a blank or null. It will be a radio button 'Yes' or 'No'. There is no way a Null can be set for that field. I can totally understand nulls in C/C++ with pointers etc. That makes complete sense and I'd be a fool to argue against the need for nulls there. But for databases where a field value is missing, it's just extra work instead of any benefits. I don't see why I'd ever need a Null. It's not like I'm using weird hacks to do whatever null is supposed to do. I'm just saying all I ever get from null is extra code on my end yet I get no benefits of detecting that something is null.

amalcon 17y ago
You can always put NOT NULL DEFAULT='' everywhere in your schema. If you do use nulls (I use them mostly in foreign keys), it will be good to have the capability, but nobody is required to have them. That said, NOT NULL probably should have been the default. It's easy to update the schema to allow nulls if you decide you need them later. You need to make up some data to replace the nulls if you decide to take them out.
ars 17y ago
There are plenty of uses for null. For example: last failed login date. What do you put if it never happened? You put NULL. As a general rule, there is no good "empty" value for things that are not strings, so you use NULL for those. For your boolean: you have a new user and want to ask if he wants the widget enabled. They could say yes or no - but until you ask, you set it to NULL, and it's an excellent flag for your application that means: ask the user. If you have never used NULLs in your databases, well, that's more a failing on your part than a failing on the concept of NULL. Sometimes you want them, and sometimes you don't - that's why the database asks you. NULL is used as the answer to x/0. NULL is used to indicate lack of records on an outer join. If you ask for the average value of something - and there are no rows, you get NULL - what else should it return? 0 is not the correct answer. > a computer that says "I don't know" is not a useful computer And a computer that returns incorrect information is worse. You asked it "what are the total sales for august". You did not ask "what are the sales so far for august". Without the NULL as the answer, how does your front end application know to collect the running average to display to the user? You most certainly do have to detect NULL. I will admit it's very rare to use NULL instead of an empty string (in fact in some versions of oracle NULL and the empty string are the same). But NULL is used frequently for other datatypes. And it's used frequently in other parts of databases.
- chime 17y ago
  Thanks for the reply. Very good points. NULL is perfect for DIV/0 or no record in join errors. I have absolutely nothing against that. It's the NULL instead of empty string that really bothers me and causes problems. Setting the field to NOT NULL has its own set of problems when used over ODBC. I know it's not the fault of the DB but the problem still remains.

by chime 17y ago

amalcon 17y ago

You can always put NOT NULL DEFAULT='' everywhere in your schema. If you do use nulls (I use them mostly in foreign keys), it will be good to have the capability, but nobody is required to have them. That said, NOT NULL probably should have been the default. It's easy to update the schema to allow nulls if you decide you need them later. You need to make up some data to replace the nulls if you decide to take them out.

ars 17y ago

There are plenty of uses for null. For example: last failed login date. What do you put if it never happened? You put NULL. As a general rule, there is no good "empty" value for things that are not strings, so you use NULL for those. For your boolean: you have a new user and want to ask if he wants the widget enabled. They could say yes or no - but until you ask, you set it to NULL, and it's an excellent flag for your application that means: ask the user. If you have never used NULLs in your databases, well, that's more a failing on your part than a failing on the concept of NULL. Sometimes you want them, and sometimes you don't - that's why the database asks you. NULL is used as the answer to x/0. NULL is used to indicate lack of records on an outer join. If you ask for the average value of something - and there are no rows, you get NULL - what else should it return? 0 is not the correct answer. > a computer that says "I don't know" is not a useful computer And a computer that returns incorrect information is worse. You asked it "what are the total sales for august". You did not ask "what are the sales so far for august". Without the NULL as the answer, how does your front end application know to collect the running average to display to the user? You most certainly do have to detect NULL. I will admit it's very rare to use NULL instead of an empty string (in fact in some versions of oracle NULL and the empty string are the same). But NULL is used frequently for other datatypes. And it's used frequently in other parts of databases.

chime 17y ago
Thanks for the reply. Very good points. NULL is perfect for DIV/0 or no record in join errors. I have absolutely nothing against that. It's the NULL instead of empty string that really bothers me and causes problems. Setting the field to NOT NULL has its own set of problems when used over ODBC. I know it's not the fault of the DB but the problem still remains.