Null values ​​in SQL Column

"null 'means" unknown data "or" non-existent value ".

Sometimes, the data corresponding to a field of a record may be unknown or not exist. In these cases we say that the field can contain null values.

For example, in our table of books, we can have null values ​​in the "price" field because it is possible that for some books we have not set the price for sale.

In contrast, we have fields that can never be empty.

Let's look at an example. We have our table "books". The "title" field should never be empty, likewise the "author" field. To do this, when creating the table, we must specify that such fields do not allow null values:

 create table books (

  title varchar2 (30) not null,

  author varchar2 (20) not null,

  editorial varchar2 (15) null,

  price number (5.2) 

 );

To specify that a field does NOT allow null values, we must put "not null" after the field definition.

In the above example, the "publisher" and "price" fields do allow null values.

When we put "null" we are saying that it admits null values ​​(case of the "editorial" field); By default, that is, if we do not clarify it, the fields allow null values ​​(case of the "price" field).

Any field, of any type of data, can be defined to accept or not null values. A "null" value is NOT the same as a 0 (zero) value or a string of blanks ("").

If we enter the data of a book, for which we have not yet defined the price, we can put "null" to show that it has no price:

 insert into books (title, author, publisher, price)

  values ​​('El aleph', 'Borges', 'Emece', null);

Note that the value "null" is not a character string, it is NOT placed in quotes.

So if a field accepts null values, we can enter "null" when we don't know the value.

We can also place "null" in the "publisher" field if we do not know the name of the publisher to which the book we are going to enter belongs:

 insert into books (title, author, publisher, price)

  values ​​('Alice in Wonderland', 'Lewis Carroll', null, 25);

An empty string is interpreted by Oracle as a null value; therefore, if we enter an empty string, the value "null" is stored.

If we try to enter the value "null" (or an empty string) in fields that do not allow null values ​​(such as "title" or "author"), Oracle does not allow it, it displays a message and the insert is not performed; for example:

 insert into books (title, author, publisher, price)

  values ​​(null, 'Borges', 'XXI century', 25);

When we see the structure of a table with "describe", in the "Null" column, "NOT NULL" appears if the field does not allow null values ​​and does not appear if it does.

To retrieve the records that contain the value "null" in some field, we cannot use the relational operators seen above: = (equal) and <> (different); we must use the operators "is null" (it is equal to null) and "is not null" (it is not null).

Null values ​​are not displayed, empty field appears.

So, for a field not to allow null values ​​we must specify it after defining the field, adding "not null". By default, the fields allow null values, but we can specify it anyway by adding "null".