Replying to Empty vs Null

Well, you can always create an index with a function, at least on oracle it's possible, don't know about mysql, on oracle you can create an index:

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?

That's right.
That means that you cannot sort, nor compare, nor index columns of a table if a null is present.
Which is rather painful for real life applications.

For me NULL is nothing

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.

an empty field means there is no data inside it, while null means it cant take in any data

Don't mix field types and field type input control.
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?

an empty field means there is no data inside it, while null means it cant take in any data

Replace a null value with dataEmpty vs Null

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

 

This question is a bit unclear nor do I have any idea what the DB actually looks like, but I will try to answer as best I can. If you wish to add data to a "blank" field you are going to need to set up a conditional statement on the applications UI or a where clause in your SQL statement. Dependent upon what exactly it is you are trying to accomplish. Keep in mind the difference between a NULL value and value that is entered as a blank string or the like. If you are creating a new row for the table then INSERT will do the trick.

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.

Add data to a blank field only
Empty vs Null

Hi

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

The concept of the NULL value is a common source of confusion for newcomers to SQL, who often think that NULL is the same thing as an empty string "". This is not the case! For example, the following statements are completely different:

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.

I think you're facing with character fields, where you have an empty string that is different from a NULL value of a character field.

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 ( "" ).