NULL

What is a NULL?

A NULL field is a field with no value.

A NULL field is a filed that was intentionally left blank.

A NULL cannot be equal or unequal to any other NULL.

Any arithmetic expression containing a NULL always evaluates to NULL. For example,

NULL + 10 = NULL

 

Nulls with Comparison Conditions

To test for NULLs, use the comparison conditions  IS NULL and IS NOT NULL.

Any other condition will result in UNKOWN, because NULL means lack of data.

 

Nulls in Conditions

A condition that evaluates to UNKOWN acts like FALSE.

–Create a table and insert a friends name

CREATE TABLE MYFRIENDS(
FIRSTNAME VARCHAR(20),
LASTNAME VARCHAR(20),
MIDDLENAME VARCHAR(20)
);

INSERT INTO myfriends (FIRSTNAME, LASTNAME)
VALUES(
‘Martin’,
‘Brooks’
‘Cameron’
);

INSERT INTO myfriends (FIRSTNAME, LASTNAME, MIDDLENAME)
VALUES(
‘Jane’,
‘Brooks’,
‘M’
);

INSERT INTO myfriends (FIRSTNAME, LASTNAME, MIDDLENAME)
VALUES(
‘Daniella’,
‘Brooks’,
NULL);

— INSERTING A value with one value missing. This will throw an error message of “not enough values” This can be solved by adding NULL where the missing value should be, see below.

INSERT INTO MYFRIENDS
VALUES(
‘David’,
‘Thomas’
);

COMMIT;
— NULL
SELECT FIRSTNAME, MIDDLENAME, LASTNAME, FIRSTNAME ||’ ‘ ||MIDDLENAME || ‘ ‘||LASTNAME AS FULLNAME
FROM MYFRIENDS;

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top