Welcome to AstaHost - Dear Guest , Please Register here to get Your own website. - Ask a Question / Express Opinion / Reply w/o Sign-Up!
Empty vs Null
Posted 10 September 2004 - 03:52 PM
Now I'm trying to use COUNT() on a field, but its returning the total number of records, which is not what I want. I know that COUNT() is supposed to count all records that arent Null, so I checked out my table in phpmyadmin and I realized that even though there are no values for some records they are not showing up as Null.
What is the difference between a Null and a field that is just empty? Shouldn't it be the same? Is there a way to count the records, or do I need to change those values to Null?
Posted 10 September 2004 - 09:37 PM
A NULL value represents the absence of a value for a record in a field (others softwares call it also a missing value).
An empty value is a "field-formatted" value with no significant data in it.
Why an "empty character string" is different from a null value? Cause you're comparing a string (with no characters inside) with a "cell" with no data type: they differ !
In mySQL an empty string "" isnt' a null value, in Excel a blank (character) cell is filled with an empty string ( "" ).
Posted 13 September 2004 - 09:25 AM
mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ("");
Both statements insert a value into the phone column, but the first inserts a NULL value and the second inserts an empty string. The meaning of the first can be regarded as ``phone number is not known'' and the meaning of the second can be regarded as ``she has no phone''.
In SQL, the NULL value is always false in comparison to any other value, even NULL. An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for the operators and functions involved in the expression. All columns in the following example return NULL:
mysql> SELECT NULL,1+NULL,CONCAT('Invisible',NULL);
If you want to search for column values that are NULL, you cannot use the =NULL test. The following statement returns no rows, because expr = NULL is FALSE, for any expression:
mysql> SELECT * FROM my_table WHERE phone = NULL;
To look for NULL values, you must use the IS NULL test. The following shows how to find the NULL phone number and the empty phone number:
mysql> SELECT * FROM my_table WHERE phone IS NULL;
mysql> SELECT * FROM my_table WHERE phone = "";
Note that you can only add an index on a column that can have NULL values if you are using MySQL Version 3.23.2 or newer and are using the MyISAM or InnoDB table type. In earlier versions and with other table types, you must declare such columns NOT NULL. This also means you cannot then insert NULL into an indexed column.
When reading data with LOAD DATA INFILE, empty columns are updated with ''. If you want a NULL value in a column, you should use \N in the text file. The literal word 'NULL' may also be used under some circumstances. See section 6.4.9 LOAD DATA INFILE Syntax.
When using ORDER BY, NULL values are presented first. In versions prior to 4.0.2, if you sort in descending order using DESC, NULL values are presented last. When using GROUP BY, all NULL values are regarded as equal.
To help with NULL handling, you can use the IS NULL and IS NOT NULL operators and the IFNULL() function.
For some column types, NULL values are handled specially. If you insert NULL into the first TIMESTAMP column of a table, the current date and time is inserted. If you insert NULL into an AUTO_INCREMENT column, the next number in the sequence is inserted.
Posted 28 May 2008 - 07:09 AM
Empty vs Null
I want to add data to a balnk field only. I have to tables. The 1 table has sum scores while the other has some scores. I want to combine both however when I do, I loose the data of the other
-question by s.Nankoo
Posted 24 June 2008 - 04:10 PM
Based on your description I would drop "the 1 table" altogether. A sum is a calculated value. You never want to include a calculated value in your DB design.
Posted 21 September 2010 - 05:38 AM
My data base has a table with emails that are generated off a form page. However, I loaded some information into my data base for testing and did not put the emails in those entries. I am try to select the emails with null values and then run the php script to generate the email and replace it in the table. Normally the script I wrote pulls the information from an html form. I need it to select the blank emails, then take the last, first, middle associated with them to process or generate the email and insert or replace it with that.I've tried several things and can only bits and pieces to clue me in on how to do this.Any assistance or lesson on the differences with what I'm asking would be ever so helpful.
-question by krazeekat
Posted 01 August 2011 - 10:02 PM
Don't mix field types and field type input control.
an empty field means there is no data inside it, while null means it cant take in any data
An empty field means that no data is inside it.
"it cant't take in any data" means that there is a control preventing you from entering data.
Else, it has a data, which is "null".
Some databases accept nulls, some databases cannot accept nulls.
For instance, for compress purposes, it's easy to imagine how to compress nothing. But how do you compress a null? Moreover, several nulls?
Posted 25 August 2011 - 01:54 PM
in most DB when you store a value like '' you will get a null, maybe it's good maybe it's bad, but for example as it was said, you can store '' an empty value, but you won't select it with the clause where something = '' only with clause where something is null. But maybe it's not on all databases.
Posted 25 August 2011 - 07:53 PM
create index my_idx on my_table (nvl(column,'null'));
And you can force to use the index you want or the optimizer will choose for you and you can use it in the where clause of a sql query.
Yeah, you can't compare null with something as you can't compare nothing with something, I can't really say, but in most cases nothing == something may return true and the statement will succeed?
Usually in an array or a table when you sort it, search algorithms make null values being at top or bottom, it depends, but the column can be sorted even if it has a null value, you always get null values if you left join a table with other table and don't have all the rows, it makes it null and you can sort by it.
It seems I can't edit my message, but maybe someone could merge it, but when comparing null with something you will get false in most cases, I said it could be true, but just tested it returns false and nothing never seems to be able to be compared and returns false?
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users