Re: Is there any way to check for a field with nothing in it?

> If I insert a new line, and put nothing into a particular field,
> what is its value? Is that what NULL is? (like, there's simply 0
> bytes allocated for its space?)

In a VARCHAR field, there are two different values that could be described as "nothing in it": one is the SQL NULL value, which is more likely the default value for a field (this can be tested for with "WHERE columnname is null ", and a zero-length string (which can be tested for with "WHERE columnname = '' "). These values are distinct.

There pretty much has to be at least one bit allocated to indicate whether the value is NULL or not if the space for the value is omitted from the record. There's a difference between SQL NULL and a zero-length string, and the database has to be able to tell the difference. If there's 3 columns in a table and a particular row has only one value in it, *which* columns are null? SQL doesn't mandate a particular representation. Databases generally try for a tradeoff between storage space and query execution speed.

In a numeric field, there is only the SQL NULL value that can be described as "nothing in it". (I don't consider a field containing the value zero to be "nothing in it".)

[Quoted] > How does one check for a NULL field, or ALL NULL fields in a
> particular line in a table?

WHERE columnname is null

There's also: WHERE isnull(columnname)

For all null fields, you have to know the names:

[Quoted] WHERE column1 is null and column2 is null and column3 is null ...

and this might cease working as intended when you add column42 if you don't also update the query. Received on Fri Jul 29 2016 - 21:11:19 CEST