What is the main Differences between Null value and blank?

[dharam_05]

Can anyone explain me what are the main difference between a null value and blank value in database. It important please help me out. thank you.

[Shanti C]

1.A NULL value represents the absence of a value for a record in a field (others softwares call it also a missing value).

2.An empty value is a "field-formatted" value with no significant data in it.

3.NULL isn't allocated any memory, the string with NUll value is just a pointer which is pointing to nowhere in memory. however, Empty IS allocated to a memory location, although the value stored in the memory is "".

4.Null has no bounds, it can be used for string, integer, date, etc. fields in a database. Empty string is just regarding a string; it's a string like 'asdfasdf' is, but is just has no length. If you have no value for a field, use null, not an empty string.

5.Null is the database's determination of an absense of a value logically, so to speak. You can query like: where FIELD_NAME is NULL

[dickersonka]

Good explanation shanti.

I would also say that null is much easiet to query for than a blank value.

[dharam_05]

Thanks guys but i have one query, if i have some null values and some empty string in the table and if i execute select * from from table. whether it will print fields having null and empty string ? what will be the o/p?

[Shanti C]

select * from tablename;

this statement will print all the columns, not the matter of null or empty..

[dickersonka]

if you need to filter out empty strings

select * from table where length(column) > 0

filter out nulls

select * from table where column is not null

if you need both, just combine

[bwagner]

i use this on a daily basis

SELECT * FROM table WHERE isnull(column,'') = ''

or other variations ....

SELECT * FROM table WHERE not isnull(column,'') = ''
SELECT * FROM table WHERE isnull(column,'') <> ''

[Stålhandske]

Although this is an old thread, I'm adding to it because it appears high in searches on the difference between null and an empty string.

This discussion and many other highly ranked discussions of the topic omit another critical difference: When a field is specified as UNIQUE, there can be multiple records with NO VALUE (i.e. NULL) in that field, but of course there cannot be multiple records with the SAME VALUE (e.g. the empty string) in the field.

That is both a logical and a useful characteristic of NULL, but not a characteristic one would like to discover late in the game.

[kamesh.veerachamy]

Hi shanti,
I read your explanation.its very clear and usefull for me.
Thanks lot.

[danielpeterson]

An vacant sequence is handled as a zero value in Oracle. Let's illustrate.

We've designed a desk known as providers with the following desk definition:

create desk suppliers
( supplier_id variety,
supplier_name varchar2(100)
);

[kamini_1]

An empty string is treated as a null value in Oracle. Let's demonstrate.

We've created a table called suppliers with the following table definition:

create table suppliers
( supplier_id number,
supplier_name varchar2(100)
);

Next, we'll insert two records into this table.

insert into suppliers (supplier_id, supplier_name )
values ( 10565, null );

insert into suppliers (supplier_id, supplier_name )
values ( 10567, '' );

The first statement inserts a record with a supplier_name that is null, while the second statement inserts a record with an empty string as a supplier_name.

Now, let's retrieve all rows with a supplier_name that is an empty string value as follows:

select * from suppliers
where supplier_name = '';

When you run this statement, you'd expect to retrieve the row that you inserted above. But instead, this statement will not retrieve any records at all.

Now, try retrieving all records where the supplier_name contains a null value:

select * from suppliers
where supplier_name is null;

When you run this statement, you will retrieve both rows. This is because Oracle has now changed its rules so that empty strings behave as null values.

It is also important to note that the null value is unique in that you can not use the usual operands (=, <, >, etc) on a null value. Instead, you must use the IS NULL and IS NOT NULL conditions.

[Vinod_11]

Hello,

So now I am still not clear about - How can I find a BLANK/EMPTY String? I got to know the difference and to find the null values but not the empty values !

Can anyone please advice on this?

Thank you in advance.

Vinod

[cereal]

@Vinod you can do:

SELECT * FROM `table_name` WHERE `column_name` = '';

This will return only the empty strings, NULL values will be ignored by the statement. An example: http://sqlfiddle.com/#!9/0aa0bb/1

[prk_1]

Hello. sorry for being thick but in the following example is the absence of data a 'null' or a 'blank'?
An Access db field titled 'Authorisation' which can either have a persons name or be left empty (i.e. no space, gap or anything else). Is this null or blank? Based on the explanation given by Shanti C below, I would think it is 'Null', because the absence of a persons name in the 'Authorisation' field is signficant, but I am confused because the absence of a name is acceptable and permitted in the relevant table, and so it is not 'a missing value'.
Note, I don't know VBA at all, I am trying to teach myself MS Access db.

1.A NULL value represents the absence of a value for a record in a field (others softwares call it also a missing value).

2.An empty value is a "field-formatted" value with no significant data in it.

Thank you

[cereal]

What's the default for the Authorisation column? It could be:

-- case 1
`Authorisation` VARCHAR(40) DEFAULT NULL

-- case 2
`Authorisation` VARCHAR(40) DEFAULT ''

I don't know how MS Access manages when the default is not defined. But following the above example: in first case when you omit the value then the column will be NULL, if you insert some data and then update to remove it, then it will be an empty string, to get NULL on update you MUST set NULL. In the second case when you insert the data and omit the value, it will be an empty string, if you update with some data and then remove it, it will be an empty string, as expected. But you could also set it to NULL.

[jacks009]

Straight up, NULL is a pointer. It’s a constant, defined in stdio.h.
Whenever you see the NULL constant used, it’s as a pointer value. It can also be used to typecast pointers, such as (int *)NULL. And keep in mind the pointer mantra:
A pointer is a variable that holds a memory location.