NULL and default values in DML

Not every field on a scroll must be filled. Sometimes the scribe does not yet know a value - a book is on loan but has not returned, so

data_zwrotu

stays empty. In the Library's language we call such emptiness NULL.

What is NULL?

NULL

is the absence of a value - not zero, not empty text, but the information "there is nothing here". This is an important distinction:

• 0

  - is the specific number zero,

• ''

  - is empty text (a text of length zero),

• NULL

  - is the absence of any value.

Inserting NULL in INSERT

To record an absence of a value, in place of the value we write the word

NULL

without single quotes:

1INSERT INTO wypozyczenia (ksiazka_id, czytelnik_id, data_wypozyczenia, data_zwrotu)
2VALUES (5, 7, '2024-03-01', NULL);

Here we set

data_zwrotu

to

NULL

- the book has been loaned out, but there is no return date yet.

Clearing a field with UPDATE

To empty an existing field (not the whole row!), we use

UPDATE ... SET field = NULL

:

1UPDATE wypozyczenia
2SET data_zwrotu = NULL
3WHERE id = 8;

Remember: this is still an

UPDATE

- so the

WHERE

applies here too! Without it you would clear

data_zwrotu

in all loans.

NULL versus a missing column

If you skip a column in

INSERT

, the Library inserts its default value (often

NULL

, and for

id

- the next number from

AUTO_INCREMENT

). Writing

NULL

explicitly has the same effect, but your intent is visible at once.